community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

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
Pulsar

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

Pulsar
Pulsar

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