Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Quick question: Calculate how many days in a month

wenjuanchen
8 - Asteroid

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!

3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

How fast and wonderful! Thank you Nicole!

adm510
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