How to convert the Excel numerical date to date in Alteryx

[Arjun]

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 

[JordanB]

Hi Arjun,

What type of date calendar is this number? 

Best,

Jordan Barker

Client Service Representative 

[Arjun]

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 · Accepted solution]

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]

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

[JordanB]

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]

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]

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 ) but this works. 

[shaynap84]

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

[JordanB]

Thisattached workflow should help explain the dialogue in this feed. 

Best,

Jordan Barker

Solutions Consultant