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
Solved! Go to Solution.
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.
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 thank you very much! That worked perfectly!
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'))
Hi @azharali34 ,
We are subtracting 2 days ( -2) because of leap year and start/end date as explained in this link .