How to convert the Excel numerical date to date in Alteryx

[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!