How to Create Date from Excel Date Formatted as Number

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,

Date Time

Preparation

Expression

Accepted answers

MikeB

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

ConvertExcelDoubleToDatetime.yxzp

All comments

FFFool

Well the date was wrong in the 12/7 file, but the 12/1 file is still a mystery to me.

FFFool

Nevermind again. I manually fixed the number in that file and got the same outcome, so there is still an issue with adding the number for 12/7.

michael_treadwell

Edited: Posted this before I saw KaneG had posted the exact same thing

The problem is that Day 0 for Excel is actually 12/31/1899. 1/1/1900 will be 1 in Excel so if you just take the integer given to you by Excel and add it to 1/1/1900 you will be 1 over.

Additionally, Excel incorrectly treats 1900 as a leap year, which it isn't. So this will also take any date past 3/1/1900 over one day.

That is where your two days are coming from.

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

MikeB

Is there a reason you are not formating the dates in Excel as dates?

If you format the column as date (except for the header row), we'll read them in as dates and do all that double to date conversion for you.

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.