What happened to my PowerPivot Excel Data? 1904? What the?
Posted by denglishbi on September 1, 2010
A few months back I had a rather unique Excel discovery. We were loading Excel data files into PowerPivot using the import Excel file option instead of the copy/paste and linked table method (for obvious reasons) and all of a sudden we noticed some issues with dates. The source data looked like the following:
But when we loaded this into PowerPivot it looked like this:
I was like…what the? Now I know I can’t stand using Excel as a source file for loading data from, but this was something I had never experienced before. Why were all of the dates on the rows imported into PowerPivot off by 4 years and 1 day? We did go ahead and try the copy/paste option and here are the results we saw:
Well now, that is what we want. But we don’t want to use the copy/paste option because we want to streamline the load process and eventually schedule data refreshes in SharePoint. So what is causing this to happen? Well it turns out that different versions of spreadsheet software have different start dates and actually Excel provides a way for users to set the starting year date to not reference 1900 (which it thinks is a leap year, but it actually isn’t) and to use 1904 instead.
This was news to me. I tracked down a Microsoft knowledge base article in regards to this issue as well XL: The 1900 Date System vs. the 1904 Date System. So since the Excel file I was loading the data into did not have the 1904 setting in place, but the source file did then this caused the dates when loaded using the import option to be offset by 4 years and 1 day. What we ended up doing was changing the source file setting to not reference the 1904 starting year and then adjusted the dates accordingly. Once we did that we were back in business. We did contact the business users and verify that this was okay and made sure they were aware of the change we were making. It turned out they didn’t even know about the setting either. Weird. Assuming someone that originally created the file must have done it for some reason.