Adding Months via Expression

[David_Dufresne]

is there a way to get the "AddMonths" expression to recognize what month it is and actually add the correct number of days?  trying to get this to fire on last day of month and then update a KW in a Unity form to reflect the next due date (last day of next month) but it just adds 30 days  😞  see image...should be 7/31/22.  every month WF will send out a reminder based off date in Unity form and then it needs to update that KW in the form to the next month and wait til timer fires again

[Larissa_Armand]

@David Dufresne -

It's not ideal, but I've had better luck with something like this: 

AddDays(Date(Year(Now());Month(Now())+2;01);-1)

We're taking today's date and adding two to the month's number bur hard-coding the first of the month. So for 4/6/2022, we are creating a date value of 6/1/2022. Then from there, subtract one day and you have 5/31/2022, the last day of next month.

Or if you pass in a property value instead of Now() you can verify you get the expected result: 

[Larissa_Armand]

@David Dufresne -

Based on Sean's comment, this would work better: 

AddDays(Date(Year(%VPropDate);
Iif((Month(%VPropDate)+2)>12;(Month(%VPropDate)+2)-12;Month(%VPropDate)+2)
;01);-1)

Instead of just adding 2 to the month, we check if that puts us with a month value greater than 12 (ex. for november 11 + 2 = 13 aka not a month) and if so, subtract 12. So again for November then we end up with 11 + 2 - 12= 1 for January. 

[Sean_Killian · Accepted solution]

I like @Larissa Armand's answer because while the expression is longer, it's clearer what it does:  It computes the 1st of the month, 2 months from now, and then decreases the day by 1 which will get you the last date of next month.  That being said, there's a small problem:  By using Month(%VPropDate) + 2, you get an error in November and December (11 + 2, 12 + 2 = 13th and 14th months which doesn't work):

I recommend an expression closer to this:

AddDays(AddMonths(AddDays(%VPropDate;1-Day(%VPropDate));2);-1)

You could replace the %VPropDate with Now(), but this expression preserves the time component of the date, so you'll get the last day of next month at the current time (plus or minus an hour if there's a DST transition one direction or another between the current date and the last day of next month).  If you don't want that, I recommend keeping %VPropDate and adding a Set Property to Expression that sets %VPropDate to this:

Date(Year(Now());Month(Now());Day(Now()))

And then put that Set Property to Expression right before the Set Property to Expression action that calculates the last day of next month.  You could technically put it all into one big expression:

AddDays(AddMonths(AddDays(Date(Year(Now());Month(Now());Day(Now()));1-Day(Date(Year(Now());Month(Now());Day(Now()))));2);-1)

But I consider that near impossible to read.  If you split it into two steps, you can name them something clear like "Put current date into %VPropDate" and "Calculate Last day of Month after %VPropDate" which is easier to understand when you look at the workflow later!

[Larissa_Armand]

Ah, thanks for pointing that out. I have had to accommodate for that before but threw this one together quickly and overlooked that.

I guess the other thing you could do is add something to check if month = 11 or 12 and set those values to 1 or 2 accordingly, otherwise use the more dynamic method. But at that point, you could do something like: 

if month = 1, 2/28 of current year

if month = 2, 3/31 of current year

...

if month = 11, 1/31 of current year +1

if month = 12, 2/28 of current year +1