DateTimeAdd to the next month end date

I have a workflow which needs to move a month end date in an XML file on to the next month end date on a monthly basis.

I had been using the below formula, but the input file this month was 30 September, and the next date after running the workflow was only going to the 30 October (not 31st).

Is there a formula to ensure we always move on to the following month end date?

DateTimeAdd([DateTime_Out],1,"month")

Workflow

Accepted answers

JosephSerpis

Hi @kcoleman a function I like to use is the datetimetrim function which allows you to use last of month in the fuction. You can wrap that function around your DateTimeAdd([DateTime_Out],1,"month") to get the last day of the month.

Datetimetrim(DateTimeAdd([DateTime_Out],1,"month"),"lastofmonth")

All comments

afv2688

Hello @kcoleman ,

How about using this? I add here two months and then subtract one day to get the last day of the month.

DateTimeAdd(DateTimeAdd(DateTimeFormat([DateTime_Out],'%Y-%m-01'),2,'months'),-1,'days')

Regards

kcoleman

Thanks, will that always find the last day of the month whether it's 30 or 31 days?

What I had done today as a temporary fix was added 1 month, 1 day) but this will only be temporary for Sept -> Oct.

I'd like something more robust where I don't have to amend each month depending on whether it's 30 or 31 days!

JosephSerpis

Datetimetrim(DateTimeAdd([DateTime_Out],1,"month"),"lastofmonth")

afv2688

hello @kcoleman ,

both solutions will give you always the last day of the month. @JosephSerpis did of course use the most elegant one which I was not aware of 🙂

Always something new to learn

Regards

kcoleman

Thank you all. Worked a charm!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703