Xplan Xmerge Coding – All About Dates

Xplan Xmerge Xplained

Xplan date coding
reference guide

All about dates

A reference guide to help with the basics and often customised date areas

Dates aren’t always the most commonly manipulated or customised areas of xplan but in the instances where you have to, this guide is going to make your life easier.

Some of the places where dates and date manipulation appear:

  • Cover letters;
  • Letter templates;
  • Used to work out the difference between periods eg time till retirement, time till age 65 etc and;
  • Portfolio and FDS templates leverage precise and sometimes complicated dating coding to achieve their ends.

The basics: datetime_merge

The most recognisable piece of date coding you are going to deal with revolves around:

<:=datetime_merge:>

# 05/06/2015 11:22:06 AM

This piece of coding will pull out the current date/month/year and time (all in short form) for when the document is merged. Usually you will see that code on letter templates, cover letters, and signature areas, really anywhere where you want the current date to appear.

Its native form (as seen above) is not likely to be what the user or client wants to see on their outputs.  We need to use the .format attribute to start creating more client friendly dates:

<:=datetime_merge.format(‘%d %B %Y’):>
# 05 June 2015

You can see by using the .format attribute we can convert the date data into a string and start representing it in more user and client friendly formats.  The .format attribute will work with any native datetime element and datetime fields.

The below is a reference guide for all the inbuilt formatting parameters.

Formatting Reference list:

Parameter

What it does

  Code example

%a

Day name – short form

  <:=datetime_merge.format(‘%a’):>
# Fri
 
%A

Day name – full form

  <:=datetime_merge.format(‘%A’):>
# Friday
 
%d

Day number – as the number of the month

  <:=datetime_merge.format('%d'):>
# 05
 
%b

Month Name – short form

  <:=datetime_merge.format('%b'):>
# Jun
 
%B

Month name – full form

  <:=datetime_merge.format('%B'):>
# June
 
%m

Month number – as a zero padded number

<:=datetime_merge.format('%m'):>
# 06
 
%y

Year number – short form

<:=datetime_merge.format('%y'):>
# 15
 
%Y

Year number – full form

<:=datetime_merge.format('%Y'):>
# 2015
 
%x

Full date based on locale settings. Should be all the same for Australian xplan sites

<:=datetime_merge.format('%x'):>
# 05/06/15
 
%X

Time with no time zone or am/pm

<:=datetime_merge.format('%X'):>
# 11:22:06
 
%H %M %S

24 hours, mins, secs – zero padded. Can be handy for time stamps and task update/modified areas.

<:=datetime_merge.format('%H %M %S'):>
# 11:22:06
 
%p

Get the AM/PM of the timestamp (in this case time of merge).  It’s unlikely you’ll find reason to use this but just like swapcase great way to mess with users.  Alternatively for client-generated documents a great way to add the nth degree of personalisation via good morning/good afternoon.

<:=datetime_merge.format('%p'):>
# AM
 

Whilst not widely acknowledged or used at this stage, in version 2.13 IRESS did build in enhanced functionality to the date formatting.  These are available from the IRESS template library but we’ve added them and in some cases enhanced them below:

Parameter

What it does

  Code example

full

Full date.  Similar to a raw datetime_merge but with the full output

  <:=datetime_merge.format(‘full’):>
# Friday, 5 June 2015 11:22:06 AM AEST
 
full, True

Full date – no time data.  You can see you need to add true to exclude the time from all these.

  <:=datetime_merge.format(‘full’, True):>
# Friday, 5 June 2015
 
long, True

Long date form no time, no dividers.
**This should be a good one to use for most client letters.

  <:=datetime_merge.format('long', True):>
# 5 June 2015
 
medium, True

Medium date with locale dividers

  <:=datetime_merge.format('medium',True):>
# 05/06/2015
 
short, True

Short date, locale dividers

  <:=datetime_merge.format('short', True):>
# 5/06/15
 
d

Day number – number of the calendar month, no zero.

<:=datetime_merge.format('d'):>
# 5
 
dd

Day number – number of calendar month

<:=datetime_merge.format('dd'):>
# 05
 
MMM

Month name – short form

<:=datetime_merge.format('MMM'):>
# Jun
 
MMMM

Month name – full name

<:=datetime_merge.format('MMMM'):>
# June
 
yy

year – short form

<:=datetime_merge.format('yy'):>
# 15
 
yyyy

Year – full form

<:=datetime_merge.format('yyyy'):>
# 2015
 
d MMMM yyyy

Day of the calendar month (no zero), full month, full year 

<:=datetime_merge.format('d MMMM yyyy'):>
# 5 June 2015
 

Key points to take away from this:

  • The enhanced (2.13 onwards) coding will automatically remove leading zeros (eg 5 June not 05 June).  This is something clients request and usually requires the use of .lstrip(‘0’) to achieve.
  • <:=datetime_merge.format(‘long’, True):> is worth remembering as this one should be popular for most clients and letters.
  • The native formatting options (1st list) are case sensitive because lower case and capital letters activate different outputs.  The enhanced formatting is still case sensitive because no redundancy was built in so make sure to keep full, long, short etc all lower case.
  • To exclude the time you must include True with any of the enhanced coding
  • Even though ‘d MMMM yyyy’ are likely to be the easiest parameters for a user to remember they aren’t included in the IRESS documentation.  Could be an oversight or may mean they wont support this style, it does work though in 2.13, 2.14, 2.15 at least.
  • It is also worth noting that datetime.date objects (non native date data, discuss more below)  won’t recognise the enhanced formatting so its still worth learning and remembering the in built formatters.

Breaking down the components

Outside of using the .format attribute you can also get the numerical (int) representations via

.day .month .year for example:

<:=datetime_merge.day:> <:=datetime_merge.month:> <:=datetime_merge.year:>
# 05 06 2015

Ideally stick to using the formatting parameters for representing dates, the above method is generally used when you want to break down the components of the date for more advanced functions or calculations (eg difference between points or times).  Remember, .format returns the date as a string so to break it down you then have to slice it.  This way you get the exact components (day/month/year) you want directly and in a numerical format we can use below.

Some of the things we can do by breaking it down include working out the difference in relative dates such as:

Client age: <:=datetime_merge.year – $client.dob.year:>

Years to retirement: <:=$client.retire_date.year – datetime_merge.year:>

Expanding on this there’s a range of times we might like to add to or subtract periods of time either to present it (SoA valid date) to work out the applicable date range (fds):

Statement of Advice valid period (+ 30 days):
<:=(datetime_merge+30 * Day).format(‘%d %B %Y’) #substitute in your advice document date field for ‘datetime_merge’:>

Capturing the last year of a period: <:=($client.disclosure_statement_date – 365 * Day)

Days till birthday:

<:if int($client.dob.format(‘%m%d’))>int(datetime_merge.format(‘%m%d’)):>

<:=int($client.dob.format(‘%m%d’))-int(datetime_merge.format(‘%m%d’)):> day(s)

<:else:>

<:=int(int($client.dob.format(‘%m%d’))-int(datetime_merge.format(‘%m%d’))+365):> day(s)

<:end:>

Those are just some basic examples to get you going and thinking of the areas where this can come in handy – these are by no means definitive eg for FDS and portfolio dates you want to take into account leap years etc. We will do a more indepth article covering how those can be better accounted for.

Suffix formatting

In some instances clients will want to include the relative ordinal suffix such as 1st January 2015 or 5th June 2015. Here are some examples of how you can achieve that (in order of easiest to read to least); I have defined our variable ‘suffix’ beforehand to make reading the coding easier – also note the use of a meaningful and obvious variable name:

<:let suffix = datetime_merge.day:>

  1. <:=’st’ if suffix in (1, 21, 31) else ‘nd’ if suffix in (2, 22) else ‘rd’ if suffix in (3, 23) else ‘th’:>
  2. <:=suffix in (1, 21, 31) and ‘st’ or (suffix in (2, 22) and ‘nd’ or (suffix in (3, 23) and ‘rd’ or ‘th)):>
  3. <:=str(suffix)+’th’ if 10 <=suffix % 100 < 20 else str(suffix)+{1:’st’, 2:’nd’, 3:’rd’}.get(suffix % 10, ‘th’):>

Putting it all together for a full date:

<:let suffix=datetime_merge.day:><:=datetime_merge.day:><:=’st’ if suffix in (1, 21, 31) else ‘nd’ if suffix in (2, 22) else ‘rd’ if suffix in (3, 23) else ‘th’:> <:=datetime_merge.format(‘%B %Y’):>

# 5th June 2015

Converting to dates

The data you want to use won’t always be in a date format.

From variable substitutions to historical data and fields, there are many occasions when you may only have a number or string such as ‘15062015’ and you want to represent that as date.

You can achieve this using the strptime and strftime functions.  Just make sure your data is in a string format or convert it accordingly:

<:let mystring=’15062015’:>

<:=datetime_merge.strptime(mystring, ‘%d%m%Y’).strftime(‘%d %B %Y’):>

# 15 June 2015

As long as you tell the function to read the data as its presented and it can match it you will be set. For example, if you only had the date stored in 2 digit format you would need to use the following:

<:let mystring=’150615’:>

<:=datetime_merge.strptime(mystring, ‘%d%m%y’).strftime(‘%d %B %Y’):>

# 15 June 2015 (change the end strfttime format if you want the output in 2 digits)

Conclusion

That covers it for most of the basic things you will need to do with dates.  You now have a reference list for all the native and IRESS enhanced date formatting, we’ve also taken a look at a lot of the common requests client’s make for their formatting and how we can manipulate date data.

I’ve mentioned that FDS and Portfolio report calculations take a bit more detail, we will cover that in a subsequent article and go over some other useful date coding and functions to achieve those.

If you come across anything you think should be in a basic article for dates and what you may need to do with them, let me know and I will add it in.

Matthew Townsend
Xplan Consultant and Developer | Wealth Management Technologist at Create Something
Matthew is an experienced and innovative Xplan consultant and developer, having worked on and developed some of the largest advice projects in the industry. Passionate about building great experiences in xplan that enable businesses and clients to get the most out of this powerful software.

Matthew Townsend

Matthew is an experienced and innovative Xplan consultant and developer, having worked on and developed some of the largest advice projects in the industry. Passionate about building great experiences in xplan that enable businesses and clients to get the most out of this powerful software.