Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Find Last Day of a Month

hemant86
11 - Bolide

Hi All,

 

I need to find the last day of a month. For example I have a date "9/10/2021" in mm/dd/yyyy format. I need to extract the last day of the month i.e. 30

Please note that I need only the last day(number 30) and not the entire date i.e 9/30/2021.

 

Thanks in Advance.

7 REPLIES 7
Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @hemant86,

 

You could use the datetimetrim() function to determine the last day of the month, i've combined it with a datetimeparse() to parse the date into a date format but you could equally do it in two seperate steps!

 

DateTimeTrim(DateTimeParse([Date],'%m/%d/%Y'),'lastofmonth')

JonathanSherman_0-1631285261596.png

 

Kind regards,

Jonathan

 

Maskell_Rascal
13 - Pulsar

Hi @hemant86 

 

Here is how you can do it. 

 

DateTimeFormat(DateTimeAdd(DateTimeAdd(DateTimeFormat(DateTimeParse([Date],'%m/%d/%Y'),'%Y-%m-01'),1,'month'),-1,'days'),'%d')

Maskell_Rascal_0-1631285134431.png

 

This formula is a lot of nested DateTime functions, but should work for you. Here is what it is doing. 

 

  • Parse date to an Alteryx Date
  • Format new date to first of the month
  • DateTimeAdd to move date to next month
  • DateTimeAdd to subtract one day to get end of month
  • DateTimeFormat to convert date to be on number day

 

Let me know if this works for you.

 

Cheers!

Phil

 

Maskell_Rascal
13 - Pulsar

@Jonathan-Sherman - I didn't realize that there was a 'lastofmonth' feature within DateTime functions. Thanks for sharing!

 

@hemant86 - using this new info, the modified formula would look like this:

DateTimeFormat(DateTimeTrim(DateTimeParse([Date],'%m/%d/%Y'),'lastofmonth'),'%d')

 

hemant86
11 - Bolide

Thanks for your response Jonathan.

 

Please see the screenshot below. I guess this is what you meant. But that does not give me what I want. Its giving the entire date and time. I need only 31.

 

hemant86_0-1631285859438.png

 

Maskell_Rascal
13 - Pulsar

@hemant86 - please see my last response. I added a DateTimeFormat to @Jonathan-Sherman's formula that will give you just the number day. 

hemant86
11 - Bolide

Thanks @Maskell_Rascal 

 

I removed the DateTimeParse function and it worked for. Thanks for all your help. With the DateTimeParse it was giving me null value

 

hemant86_0-1631286419398.png

 

Maskell_Rascal
13 - Pulsar

@hemant86 - Glad it worked for you! Make sure to mark this post as solved for anyone else that may need help and find it. 

Labels