Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to convert the Excel numerical date to date in Alteryx

Arjun
7 - Meteor

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 

15 REPLIES 15
mix_pix
10 - Fireball

This proved very helpful today.  Thank you!

JenniferLaVite
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?

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

 

mbogusz
9 - Comet

Thank you Jordan!

pratibha1720
5 - Atom

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
11 - Bolide

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

Labels