Last day of the next month

[joannasokolowska]

Hey guys,

Quick question  - I need to calculate the last day of next month for given date (already in date format)  e.g. - 2021-07-09

For given example the output should be - 2021-08-31

Thanks,

Asia

[Luke_C · Accepted solution]

Hi @joannasokolowska 

You could try a datetimeadd + datetimetrim function. Replace field1 with your field. The formula is adding a month to your date and then getting the last day of that month.

datetimetrim(datetimeadd([Field1],1,"month"),'lastofmonth')

[atcodedog05]

Hi @joannasokolowska 

Here is the formula:

I am taking first of current month, adding 2 month - 1day to get last of next month.

Hope this helps 🙂

[atcodedog05]

Hi @Luke_C 

Your method is better and sleek. I will taking it for reference. Thank you 🙂👍

[apathetichell]

I usually use this. looks like a mess but it works:

datetimeadd(datetimeadd(datetimeadd([field1],-tonumber(datetimeformat([field1],"%d"))+1,"days"),2,"months"),-1,"days")

it subtracts the number of days the date is into the month (-1). Then adds two months. It then subtracts one more day.

this would all be easier if datetimefirstofmonth() and datetimelastofmonth() took parameters.

[joannasokolowska]

It works great! thank you!