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

Convert field to date with multiple formats

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


You should be able to follow the solution found here:


"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. 

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




6 - Meteoroid

@benakesh thank you very much! That worked perfectly!