How to convert the Excel numerical date to date in Alteryx

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

Date Time

Accepted answers

JordanB

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-96e4-32a67ec0a680 for your reference.

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

Best,

Jordan Barker

Client Service Representative

Date Converter.yxmd

All comments

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!

paulfound

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

DateTest.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703