Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

2022-05-24 Updates: Login: If you are facing issues with logging in, please try clearing all your cookies or trying another browser. We have a fix being deployed on the evening of 5/25. Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.
alteryx Community

# Alteryx Designer Discussions

SOLVED

## 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

5 REPLIES 5
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"

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') ,
DateTimeParse([date],'%m/%d/%Y'))

6 - Meteoroid

@benakesh thank you very much! That worked perfectly!

5 - Atom

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

12 - Quasar

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

Labels