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")
Solved! Go to Solution.
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
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!
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")
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
Thank you all. Worked a charm!