Tutorial Videos

Tutorial videos are available here.

Knowledge Base

Visit our User Forum for discussions & solutions

Get the Last Day of the Month

Suppose you want to get the date of the last day of a month.

There are a number of ways to achieve this with XpressDox.

With version 8.2.1 of XpressDox, this can now very easily be achieved using the LastDayInMonth function.

Brute Force Method

Assume that you have a data element containing the month name, either in full or abbreviated.

«Case(PaymentDueEndOf = ‘Jan’)»«setV(‘lastDay’,’31/1/’)»«End(jan)»
«Case(PaymentDueEndOf = ‘Feb’)»«setV(‘lastDay’,’28/2/’)»«End(feb)»
«Case(PaymentDueEndOf = ‘Mar’)»«setV(‘lastDay’,’31/3/’)»«End(mar)»
«Case(PaymentDueEndOf = ‘Apr’)»«setV(‘lastDay’,’30/4/’)»«End(apr)»
«Case(PaymentDueEndOf = ‘May’)»«setV(‘lastDay’,’31/5/’)»«End(may)»
«Case(PaymentDueEndOf = ‘Jun’)»«setV(‘lastDay’,’30/6/’)»«End(jun)»
«Case(PaymentDueEndOf = ‘Jul’)»«setV(‘lastDay’,’31/7/’)»«End(jul)»
«Case(PaymentDueEndOf = ‘Aug’)»«setV(‘lastDay’,’31/8/’)»«End(aug)»
«Case(PaymentDueEndOf = ‘Sep’)»«setV(‘lastDay’,’30/9/’)»«End(sep)»
«Case(PaymentDueEndOf = ‘Oct’)»«setV(‘lastDay’,’31/10/’)»«End(oct)»
«Case(PaymentDueEndOf = ‘Nov’)»«setV(‘lastDay’,’30/11/’)»«End(nov)»
«Case(PaymentDueEndOf = ‘Dec’)»«setV(‘lastDay’,’31/12/’)»«End(dec)»

You must pay before «GetV('lastDay')»«Today('yyyy')»

This is OK as far as it goes, but leap years are not catered for, and it’s a LOT of typing.

Subtle Brute Force Method

Using the Lookup function, a lot less typing is needed (US date sequence used in this example):

You must pay before «Lookup(PaymentDueEndOf,'Jan:1/31;Feb:2/28;Mar:3/31;Apr:4/30;May:5/31;Jun:6/30;Jul:7/31;Aug:8/31;Sep:9/30;Oct:10/31;Nov:11/30;Dec:12/31')»/«Today('yyyy')»

Only one line is needed to get the required value, but still leap years are not catered for.

Elegant Solution

The rationale behind the elegant solution is that if we have a month name or number, then we can use the built-in date functions of XpressDox to work out the last day of the month, and even take leap-years into account. The idea is to use the IncrementDate function to get the date of the first day of the next month, and then, still using IncrementDate, subtract one day, which will then give us the last day of the month we’re working with.

The name of the month is known

This example is done using variables to illustrate the various steps required, but in the end only one fillpoint is needed:

Firstly, using the name of the month in the data element MonthName, construct a date representing the 1st of that month:

«SetV(‘FirstOfMonth’, concat(MonthName, ‘ 1 ’,Today(‘yyyy’) ))»

So, if MonthName contained “Feb” or “February”, the value of variable ‘FirstOfMonth’ would be “Feb 1 2014”, or “February 1 2014” (if run in the year 2014).

Then, using IncrementDate, add one month to the ‘FirstOfMonth’ value:

«SetV(‘FirstOfNextMonth’, IncrementDate(Getv(‘FirstOfMonth’),1, ‘m’))»

In our example, this would get the date “March 1 2014” into the ‘FirstOfNextMonth’ variable.

Finally, get the date of the last day of February into the variable ‘LastDayOfMonth’, again using IncrementDate, but subtracting one day from the ‘FirstOfNextMonth’:

«SetV(‘LastDayOfMonth’, IncrementDate(Getv(‘FirstOfNextMonth’),-1, ‘d’))»

And, finally, format the last day in whatever way you would like, using FormatDate, e.g.
«FormatDate(Getv(‘LastDayOfMonth’), ‘MMMM dd yyyy’)»

Once you have that under your belt, you can dispense with all the intermediate variables, and just code the whole lot in one fillpoint:

You must pay before «FormatDate(IncrementDate(IncrementDate(concat(MonthName, ‘ 1 ’,Today(‘yyyy’) ),1, ‘m’),-1, ‘d’), ‘dd MMM yyyy’)».

The number of the month is known

This is very similar to the first example, all that is needed is for the inner-most concat to create a date of the form “yyyy-MM-01” (this is the official XML Schema date format which, specifically, removes ambiguity around interpretation of day number and month number, which means in turn that the function below will work regardless of the regional settings of the PC on which it runs):

You must pay before «FormatDate(IncrementDate(IncrementDate(concat(Today(‘yyyy’),‘-’ , MonthNumber,‘-01’),1, ‘m’),-1, ‘d’), ‘dd MMM yyyy’)».

The full date is known

Again, quite similar to the above two, in that the inner concat constructs the date as the first day of the month according to the FullDate data element’s value:

You must pay before «FormatDate(IncrementDate(IncrementDate(concat(FormatDate(FullDate,‘yyyy’),‘-’,FormatDate(FullDate,‘MM’), ‘-01’),1, ‘m’),-1, ‘d’), ‘dd MMM yyyy’)».