Quick question: Calculate how many days in a month

[wenjuanchen]

A quick question:

Is there a function in Alteryx to calculate the number of days in a month? For example, for January, it gives 31, for September, it gives 30, for February in a Leap year, it gives 29, etc.

Thank you in advance!

[NicoleJohnson · Accepted solution]

Not sure if there is a faster way, but this formula should do the trick:

DateTimeFormat(DateTimeAdd(DateTimeAdd(DateTimeTrim([Field1],"month"),1,"month"),-1,"day"),"%d")

Basically, it takes whatever date you put in there, trims it to the month (i.e. 2017-11-01 for original date 2017-11-29), and then adds a month, subtracts a day, and formats the datetime string to show just the day.

Bit round-a-bout, but it gets there :)

Cheers,

NJ

[wenjuanchen]

How fast and wonderful! Thank you Nicole!

[adm510]

DateTimeTrim([field_1],"lastofmonth") will give you the last day of the month, and to grab the actual day part, you can use this: DateTimeFormat([Field1],"%d")

Putting it all together: DateTimeFormat(DateTimeTrim([Field1],"lastofmonth"),"%d")