How to convert the Excel numerical date to date in Alteryx

[Arjun]

Hi 

I have a date column in Excel that contains dates in the numerical format (for example 42349 would be 12/11/2015). I have tried parsing or changing the format of the column but Alteryx does not support this kind of a date and generates a null value when operated upon.

I am trying to automate the process of preparing a model so changing th format of the date in Excel is not an option. Also, the data exceeds 1 million rows so Excel won't support it either. How can change this to a date format?

Thanks,

Arjun 

[mix_pix]

This proved very helpful today.  Thank you!

[JenniferLaVite]

I am attempting to do the opposite.  My date is in mm-dd-yyyy format, but I need the Excel 5 digit, post 1900 number.  IS there a way to convert the date?

[Jon_H]

RodL--Thank you very much for your solution.  After trying it out, I realized your logic was mostly sound but wasn't quite fool-proof since I still got times that were off by 1 sec occasionally.  I used your thought process to sketch out a full, fool-proof solution that got me the right answer 100% of the time for the full date & time.

Step 1 - Create the base set of data to work with (all "double" data types):

• DAY_DECIMAL:  [ExcelDateTimeVal]
• DAY_ROUNDDOWN:  FLOOR([ExcelDateTimeVal])
• TIME_DECIMAL:  [ExcelDateTimeVal] - FLOOR([ExcelDateTimeVal])
• HR_DECIMAL:  [TIME_DECIMAL] * 24
• HR_ROUNDDOWN:  FLOOR([HR_DECIMAL])
• MIN_DECIMAL:  ([HR_DECIMAL] - [HR_ROUNDDOWN]) * 60
• MIN_ROUNDDOWN:  FLOOR([MIN_DECIMAL])
• SEC_DECIMAL:  ([MIN_DECIMAL] - [MIN_ROUNDDOWN]) * 60
• SEC_ROUNDDOWN:  FLOOR([SEC_DECIMAL])
• SEC_ROUND:  ROUND([SEC_DECIMAL], 1)

Step 2 - Resolve the correct date & time parts one at a time using only the base data set (doing this eliminates having to perform the steps in any particular order):

• SEC_RESOLVED:  IIF([SEC_ROUND]=60,0,[SEC_ROUND])
• MIN_RESOLVED:  IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60, 0, IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN]))
• HR_RESOLVED:  IIF(IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN])=24, 0, IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN]))
• DAY_RESOLVED:  IIF(IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN])=24, [DAY_ROUNDDOWN]+1, [DAY_ROUNDDOWN])

Step 3 - Resolve the date portion of the date & time:

• DATE_RESOLVED:  DateTimeAdd("1900-01-01 00:00:00",[DAY_RESOLVED]-2,"days")

Step 4 - Resolve the full date and time using [DATE_RESOLVED] created in Step 3:

• DATETIME_RESOLVED:  DateTimeAdd(DateTimeAdd(DateTimeAdd([DATE_RESOLVED],[HR_RESOLVED],"hours"),[MIN_RESOLVED],"minutes"),[SEC_RESOLVED],"seconds")

[mbogusz]

Thank you Jordan!

[pratibha1720]

We can simply do:

1) First change the 'Dummy_Date' field data type with 'double'

2) then in formula tool use ToDateTime([Dummy_Date]) and change data type with 'DateTime'.

Done

[paulfound]

I know this is an old ticket but I was looking for this solution and this is the ticket that got me started.

I have come up with something a bit slimmer, I have tested it and it works as far as I can see. I have added the example workflow.

If the Date Serial is 43831.588530092595 first thing is to separate the whole number and the decimal.

Date:

Whole Number = 43831 = Number of days since 1900-01-01 (you need to take 2 days off, due to excel leap year error and to account for the first day)

DateTimeAdd("1900-01-01",43831-2,"days") = 2020-01-01

Time:

Decimal = 0.588530092595 = fraction of seconds since 00:00:00. Find the number of seconds since midnight by 1/86400 which is equivalent to a 1 second as a fraction. 0.588530092595 / (1/86400) is 50849.

DateTimeAdd("2020-01-01",50849,"seconds") = 2020-01-01 14:07:29

* I do some rounding as I found if the decimal was to long it would drop a second.

Sorry if this isn't the best explanation, hope the actual workflow helps. 😀 

[Coakles]

Seriously life saver!

[daiphuongngo]

Hi Jordan. Thank you for your solution. Could you explain the logic of the 2 formulas on the right side please?