Imported dates earlier than 1900 not working in Excel 2010

June 21, 2012 at 09:34:36
Specs: Windows 7

I am using a SQL query to import data into Excel. The database has some time fields which are formatted as datetime rather than just time, so each entry is in the format yyyy-mm-dd hh:mm.

In all cases, the time is less than 24 hours (this is the time recordal program for our consultants) per entry, so the date element is always the same. For some reason, the date is always shown as 1899-01-01 in the database, and this has never been an issue when using Excel 2003 - the date simply displayed as 00-01-1900 and then the relevant time was shown. For example, if some had spent 1 hour 30 minutes on a job, the relevant entry, when imported to Excel, would be 00-01-1900 01:30.

However, since 'upgrading' to Excel 2010, the above example now returns 00-01-1900 00:00, and switching the format of the cell to Number or General shows the value as being 0.

This used to work fine in Excel 2003, so any ideas on what has changed in Excel 2010?


See More: Imported dates earlier than 1900 not working in Excel 2010

June 24, 2012 at 05:36:22

Report •

June 25, 2012 at 02:12:05
Thanks Phil - useful workarounds, I did search a few forums and websites before posting on here.

While those suggestions can be a useful, it does not explain why the dates previously worked in Excel 2003 but not in Excel 2010.

As you can imagine, we have a number of spreadsheets pulling data from this database, so I am trying to understand what the difference is between the two versions to cause this...

Report •
Related Solutions

Ask Question