Convert field to date with multiple formats

[lil_t]

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

[BrandonB]

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"

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. 

[benakesh · Accepted solution]

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

[lil_t]

@benakesh thank you very much! That worked perfectly!

[azharali34]

Hi @benakesh - Can you explain why do we use "-2" in the formula below?

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

[benakesh]

Hi @azharali34 , 

We are subtracting  2  days ( -2)  because of leap year   and  start/end date  as  explained  in this link .

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-convert-the-Excel-numerical-dat...