Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Quick question: Calculate how many days in a month

Highlighted
Alteryx Certified Partner

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!

Highlighted
14 - Magnetar
14 - Magnetar

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

Highlighted
Alteryx Certified Partner

How fast and wonderful! Thank you Nicole!

Highlighted
11 - Bolide

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

 

Labels