Alteryx Designer Discussions

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

Last day of the next month

joannasokolowska
8 - Asteroid

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

5 REPLIES 5
Luke_C
14 - Magnetar

Hi @joannasokolowska 

 

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

 

 

Luke_C_0-1622121339018.png

 

atcodedog05
20 - Arcturus

Hi @joannasokolowska 

 

Here is the formula:

atcodedog05_0-1622121442521.png

 

I am taking first of current month, adding 2 month - 1day to get last of next month.

 

Hope this helps 🙂

atcodedog05
20 - Arcturus

Hi @Luke_C 

 

Your method is better and sleek. I will taking it for reference. Thank you 🙂👍

apathetichell
16 - Nebula

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.

joannasokolowska
8 - Asteroid

It works great! thank you!

Labels