Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to convert the Excel numerical date to date in Alteryx

10 - Fireball

This proved very helpful today.  Thank you!

5 - Atom

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?

5 - Atom

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

 

8 - Asteroid

Thank you Jordan!

Labels