2

I'm running Excel 2007.

I've done a dump of data from a MSSQL database into a CSV file, then saved it as part of a larger Excel book. One of the fields is a date, which downloads as: 39262.3631094907. When I use Excel's date format it comes up correctly as 6/30/2011.

If I save the file as CSV, or otherwise copy and paste this field into a new spreadsheet, the date changes to 6/29/2007. I can copy it within the same document, and it stays correct, but moving it to a new doc changes the data.

This is driving me crazy as I can't find any way to keep the right date without manually retyping them all, which is completely impractical because there are thousands of lines! I have no idea how it manages the conversion of string to number, or why it's different in different spreadsheets. Does anyone know why this is happening or how to keep this from happening?

Excellll
  • 12,847
THill1981
  • 165

1 Answers1

1

The number you got from your MSSQL will be treated by Excel as the number of days since 1900 or 1904, depending on your Excel workbook settings. It sounds like your new workbooks are defaulting to the 1900 date system but you want 1904.

To change in Excel 2007:

  1. Open or switch to the workbook.
  2. Click the Microsoft Office Button, and then click Excel Options.
  3. Click Advanced.
  4. Click to select the Use 1904 data system check box under the When calculating this workbook, and then click OK.

http://support.microsoft.com/kb/214330

Spamwich
  • 320