How to Create Date from Excel Date Formatted as Number

[FFFool]

I've run into this issue. I have many files where the date that I need access to is formatted as a number in Excel. I did some research on the number and found that this number is the number of days that had passed since 1/1/1900. 

With this information at the ready, I created a calculation that loked at the date to determine if it was a number. If it was a number, then it did a DateTimeAdd Function of 1/1/1900 + the number formatted date field from excel. Otherwise it just returned the date. 

I quickly realized that almost all of my date fields were exactly 3 days off. So, I subtracted 3 days from the number formatted date field. This causes the date to be correct for 98% of my files. However, every so oftern(Particularly a file that should have had the date 12/1 and another that should have had the date 12/7 require that the subtraction be only 2 days, rather than 3. 

If I can identify the cause, I could at least build an IF function for it, but even that makes me nervous unless I know the root issue. Obviously writing an excel macro to fix the date formats is possible, but I really would like it to be my last resort incase I run into this issue again. 

Any help is appreciated. 

Thank you,

[FFFool]

Hey Mike, 

Yes, because previously we didn't need this date as we had a different system of identifying what the date value should be for our trending that was more of a manual/vlookup process. I'm automating wherever possible at this point, so doing it that way is not an option. Whoever created the initial macro in excel, didn't change that column to a date format. Now I have ~500 fairly large files that don't have this particular date field formatted as a date. From here on out, that Excel Macro is being converted to Alteryx, and the date format problem will be fixed. However, for creating a historical database, I can't really go back in time and change the macro, and I was trying to avoid spending a few hrs opening, adjusting, and saving each file.

[FFFool]

I'll give it a go with the 1899 date instead of the 1900 date, and since all my dates would be past 3/1/1900, I would then still need to make a 1 dat adjustment, correct? 

My confusion arose when I took 1/1/1900 + the number of days from the 12/6 file  -3 and got 12/6. Then I did the same equation with the 12/7 file and got 12/6 again instead of getting 12/7. 

I need to put this project away for now as I've found a way to at least provide the same data I was before, though not more detailed like I was hoping. I will dive back into it next year and try again once I have some more time on my hands. 

Thanks,

[MikeB]

Ok, that makes sense.

You should be able to do the conversion, like we are doing in the Alteryx code.

If you google convert double to date you'll see that there are a lot of ways to do it.

A value of 0 is Dec 30, 1899 at midnight - due to the Lotus 1-2-3 leap year bug which Excel inherited.

All dates between Jan 1, 1900 and March 1, 1900 are off by one because of this.

So I believe your calculation needs to count the number of days since Dec 30, 1899, not Jan 1, 1900.

Also, beware of rounding errors that can occur when doing math with doubles.

we offset the value by a half second in order to avoid rounding errors:

        // Offsetting by a 1/2 second to avoid rounding errors
        excelDateValue += 0.5 / (24 * 60 * 60);

We then use the new value to do our calculations with. I can't remember if the rounding we saw was

in the date or time components, or both. Your comment that some dates are off by one made me

think that the rounding might be the problem.

I hope this is helpful
    

[Quantish]

Hello, 

I have run into the same issue, my Excel dates are stored as date values and i need to convert them into a real date.

The source file is 1.6M lines so too big for Excel to read and convert, would you be able to share your formula to reconvert them? 

thanks

[MikeB · Accepted solution]

Here's a quick workflow that converts a double (Excel date) to a Datetime in Alteryx

See attached workflow.

It uses this function in a formula tool:

DateTimeAdd([Datebase],[Field1],"days")

where Datebase = a datetime value set to "1899-12-30 00:00:00"

and Field1 is my double value = 42397

The answer if you run the workflow is today's date: "2016-01-28 00:00:00"

Hope that helps

[Quantish]

I'll try it right away! 

thanks

[MikeB]

Keep in mind that the double value may have a fraction (significant digits to the right of the decimal point).

The decimal part relates to the time.

For example 42397.5 would be Noon instead of midnight (which is 42397.0)

and 42397.25 is 6am

so, if you have a time component, you'll have to deal with that part separately.

The formula I gave you will take the whole number part and convert that to a date, but won't get you the time.

[Quantish]

Hello, 

My number doesn't have a decimal fortunately ;-) 

However I know bump into a new issue, it takes the first input of the formula but doesn't recognise the value in the array to be added. 

Specifically the error message says: 

Error: Formula (120): The formula "Date" resulted in a string but the field is numeric. Use ToNumber(...) if this is correct. (Expression #1)

I tried using the ToNumber function but i get the same message again

Any idea why that may be?

Thanks

J.

[MikeB]

Make sure the field type in the formula is Date or Datetime not Numeric (like Int32)

[amit5182]

Thanks Mike. 

I tried that already before posting. Time component is very important for me and the example in the other link gives only date.

Thanks

Amit