community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

How to convert the Excel numerical date to date in Alteryx

Fireball

This proved very helpful today.  Thank you!

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?

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

 

Asteroid

Thank you Jordan!

Labels