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

Formula to arrive at date when I have Day, Month and Year in separate columns

Upansu
5 - Atom

Can someone please help me to arrive at the date, when I have the day, month and year in three different columns.

 

Any help will be appreciated.

 

DayMonthYear Desired Output
10122009 10-12-2009
862018 08-06-2018
1912000 19-01-2000
2 REPLIES 2
DavidP
17 - Castor
17 - Castor

The formula would be [Day]+'-'+[Month]+'-'+[Year] for your new field, but please note that the Alteryx standard date format is yyyy-mm-dd.

Kenda
16 - Nebula
16 - Nebula

Hi @Upansu!

 

@DavidP is correct in that you can just append the fields together using the plus sign and quotes. Here are a couple additional specifications if you need them:

 

  • The fields must already be strings coming into the Formula tool OR you can use the tostring() function each time you reference one of the field names if they're numeric.

 

  • If you need the leading zero's and the day or month number is only one digit, you could adjust the formula to look like this:
iif(length([Day])=1,"0"+[Day],[Day])+"-"+iif(length([Month])=1,"0"+[Month],[Month])+"-"+[Year]

This should still be output as a new string field.

 

 

  • If you want your new field to be a date type, adjust the formula to look like this:
datetimeparse([Day]+"-"+[Month]+"-"+[Year],"%e-%m-%Y")

This will allow Alteryx to recognize your new field as an actual date field if you need to perform any calculations on it or anything downstream and will output the field in Alteryx's yyyy-mm=dd format.

 

Labels