DateTimeAdd to the next month end date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you all. Worked a charm!