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

Convert field to date with multiple formats

Highlighted
6 - Meteoroid

I have a column that has 2 dates that are formatted like 43769 but also 11/26/2019. How would I convert them to dates?

 

When I convert 43777 to a date in excel it changes to 11/08/2019.

 

Thank you

Highlighted
Alteryx
Alteryx

You should be able to follow the solution found here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-convert-the-Excel-numerical-dat...

 

"use the alteryx DateTimeAdd(dt,i,u) function to add the number of days (42349) minus 2 to 1900-01-01 to get the correct date"

 

date conversion.png

 

 

Because you have two types of dates in the field, it might be best to use a filter beforehand where length([Field1])=5. Then you can pass these values into the formula and convert the values from the false using a date time tool, and then union them back together. 

Highlighted
12 - Quasar

Hi @lil_t ,

You can check the format of date and apply appropriate formula to  convert to date . 

iif( REGEX_Match([date] , '\d{1,5}') ,
datetimeadd('1900-01-01',tonumber([date]) - 2 , 'days') ,
DateTimeParse([date],'%m/%d/%Y'))

 

benakesh_0-1576631077013.png

 

6 - Meteoroid

@benakesh thank you very much! That worked perfectly!

Labels