Adding Months to Date Fields while retaining the last day of the Month

Problem

Sometimes you will need a date field to store the last day of a particular month, while also running updates on it to push it forward every month as time passes. For instance, if a particular deadline is always on the last day of the month, and you want this to always show that date each month, then you might run an action to add one month to it on a monthly basis.

The problem with simply adding 1 month with a formula however (i.e. $datefield + 1 Month), is that this will always retain the day of the month that is currently in the field. In other words, if today were February 28th, adding a month would result in the field becoming March 28th, which is not the last day of the month.

Solution

The solution is quite simple, and is basically to just add one day to the current date value first (thereby pushing it to the 1st day of the following month), then add one month to that value before subtracting a day again, bringing it back to the last day of the intended month.

So instead of using the following formula for simply adding one month:
$datefield + 1 Month

You would use the following formula:
(($datefield + 1 Day)+ 1 Month) - 1 Day

This will result in the date field always being updated with the last day of each month, regardless of how many days there are in that month.

Similarly, if you have the same requirements as above, but with quarters instead of months, the same principal and approach can also be applied (i.e. add one day, then add three months, then subtract one day).

1 Like