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]

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. 

[KaneG]

Hi,

What year are these dates in? Can you provide an excel file with the incorrect dates?

@Ned has given an explanation here about where the errors in Excel date formatting come from on dates in early 1900. The difference of 3 will most likely be, 1 day for Excel thinking 1900 was a leap year, 1 day for 1900-01-00 incorrect date in Excel, and 1 day because 1/1/1900 is being double counted.

I can't say for sure without seeing the data, but the above should give you an idea as to where the issue may lie. There is some other information on the issue at the Microsoft KB.

Kane

[MarqueeCrew]

Lots of interesting reading in the knowledgebase about Excel date issues.  I want to ask why we are looking at dates back in the 1900's, but I'm afraid to ask.

[FFFool]

haha, 

I'm looking at 2015 dates. Problem is when an Excel Date is formatted as a number, it comes up as the number of days since 1900, or so they say. So I'm creating a formula that takes 1/1/1900 and adds the number of days in the Excel Date column. These other issues cause it to be a few days off and I'm running into specific situations where some days are off 3 and some are only off 2, which is making my adjustment quite hard. 

I need to look over and reply to the other poster once I get some time to mock up an example. 

[MarqueeCrew]

I wonder if using the 1904 date option would help you in Excel.

[FFFool]

Thanks for the suggestion. Unfortunatley, the point of this was that I had a file for each day, dating back to last year, that I was loading into Alteryx using the Wildcard macro to create a historical database. If I wanted to open each file and do something, I would just change them to a date format, the point was to find a solution in Alteryx so I could avoid that.

I ended up just pulling in my Excel database I had been maintaining instead of recreating the database from all of the files. if I could have recreated, I would have been able to provide more segmented info than the previous report was capable of, but being able to keep what I already have is certainly a start. 

Thanks again.

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