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
Solved! Go to Solution.
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')
Here is the formula:
I am taking first of current month, adding 2 month - 1day to get last of next month.
Hope this helps 🙂
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.
It works great! thank you!