Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

DateTimeAdd to the next month end date

kcoleman
6 - Meteoroid

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")

5 REPLIES 5
afv2688
16 - Nebula
16 - Nebula

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
6 - Meteoroid

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
16 - Nebula
16 - Nebula

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")

 

 

afv2688
16 - Nebula
16 - Nebula

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
6 - Meteoroid

Thank you all. Worked a charm!

Labels