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
JordanB
Alteryx
Alteryx

Hi Arjun,

 

What type of date calendar is this number? 

 

Best,

 

Jordan Barker

Client Service Representative 

Arjun
7 - Meteor

Hi Jordan

 

Thanks for getting back.

 

I don't exactly know what you mean by date calender but this is what I know (copied from the web):

 

Excel (by default) uses the 1900 date system. This simply means that the date 1 Jan 1900 has a true numeric value of 1, 2 Jan 1900 has a value of 2 etc. These values are called "serial values" in Excel and it is these serial values that allows us to use dates in calculations.

 

If you put in any number > 0 in Excel and change the format to "Short Date" or any of the other date formats, it wil convert it to a date using the definition above. Hope this is what you were asking for. Thanks!

JordanB
Alteryx
Alteryx

Hi Arjun,

 

That was a big help!

 

I have attached a workflow whereby you can insert your excel file, and all you need to do is update the formula tool with the field with holds your excel date values. 

 

All I did was use the alteryx 'dateadd' function to add the number of days (42349) to 1900-01-01 to get the correct date. I got this formula from https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96... for your reference.

 

Let me know if this is what you are looking for,

 

Best,

 

Jordan Barker

Client Service Representative 

Arjun
7 - Meteor

Thank you so much Jordan! Really appreciate all the help :)

JordanB
Alteryx
Alteryx

Hi Arjun,

 

There is a slight amendment needed to that date conversion module you posted. You need to minus 2 off the number provided by Excel, due to the issue with 29th Feb 1900 and the inclusiveness of the start/end dates. (1900-01-01 is day 1 not day 0, and 1900 was not a leap year but excel calculates it wrongly).

 

Details at https://support.microsoft.com/en-us/kb/214326

 

Test 42388, as this should be 2016-01-19…

 

Thanks to @kane_glendenning also

 

Best,

 

Jordan Barker 

Client Service Representative 

inder1
5 - Atom

hi, I am trying to do the similar thing.

my source data has the date in numerical formate as here under

42401.04376

42401.04563

 

in alteryx, it shows v_string in alteryx when i imported. I am trying to get the out put as "mm/dd/yyyy hh:mm:ss"

 

I used the function you suggested, but two problems with it.

1. the first error msg, "Argument 2 of DATETIMEADD is not not an integer"

2. when i convert that field to int64 the, it gives error as "lost information in translation" for each record :(

 

 

 

RodL
Alteryx Alumni (Retired)

One thing you can do is split the data into two components...the day (which is the whole number) and the time (which is the decimal part).

Once you have that, you can convert the day using the DATETIMEADD function. You can then convert the decimal part into a time and then concatenate the two results back for your full date time.

I've attached a workflow that shows an example of how to do the latter. There's probably more elegant ways to do this (for which I will let the "regular responders" chime in Smiley Wink) but this works. 

shaynap84
5 - Atom

does this work?  your second comment about adding 2 to the module was confusing.

 

 

JordanB
Alteryx
Alteryx

Thisattached workflow should help explain the dialogue in this feed. 

 

Best,

 

Jordan Barker

Solutions Consultant

 

Labels