Find Last Day of a Month

[hemant86]

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.

[Jonathan-Sherman]

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

Kind regards,

Jonathan

[Maskell_Rascal]

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

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]

@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]

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.

[Maskell_Rascal]

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

[hemant86 · Accepted solution]

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

[Maskell_Rascal]

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