Alteryx Designer Discussions

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

Find Last Day of a Month

hemant86
9 - Comet

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
9 - Comet

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
9 - Comet

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