Find Last Day of a Month

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.

Date Time

Accepted answers

hemant86

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

All comments

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703