cancel
Showing results for 
Search instead for 
Did you mean: 

Adding Months via Expression

David_Dufresne
Confirmed Champ
Confirmed Champ

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

 

0e73cf57e1064ed6b58e6b31c51cacd4

1 ACCEPTED ANSWER

Sean_Killian
Elite Collaborator
Elite Collaborator

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):


c5080e18fe5e4aadbfeba8792be52e92


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!

View answer in original post

8 REPLIES 8

Larissa_Armand
Elite Collaborator
Elite Collaborator

@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.

 

3fbb47dc2aa94f199e1e92854ac0ed8b

 

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

 

f8e85e138d7747f980a0d41d5f18149a

 

91088df80b9b42838065a02273d56da4

 

eb68d0a5efb74f0a8f12359980dc30db

@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. 

 

ced71b84fba945eb8d566035b92c549a

Sean_Killian
Elite Collaborator
Elite Collaborator

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):


c5080e18fe5e4aadbfeba8792be52e92


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!

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

Getting started

Find what you came for

We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.