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?

Improve this question
3
  • 1
    I cannot replicate this particular inconsistency, but FYI, within excel, 39262 IS 6/29/2007 (40724 is 6/30/2011). Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. So, I think the real question is what date format is your MSSQL using, and is there a consistent offset one can use (1462?)
    – Madball73
    Commented Mar 6, 2014 at 17:37
  • I don't understand the details of how it converts, but am I correctly reading between the lines that the whole number (39262) is the date and the decimal (.363....) is the time? Either way, you might be right that I can just add a number to get the right output. It's awkward, but as it's not my MSSQL db, it's the best option I've heard sofar. I'll give that a try and let you know if it works...
    – THill1981
    Commented Mar 6, 2014 at 17:50
  • See answer below. The MS KB is quite clear I think.
    – Madball73
    Commented Mar 6, 2014 at 17:58

1 Answer 1

Reset to default
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

Improve this answer
2
  • 2
    @OP Note in the KB, you can set this per workbook. So, my guess is this "larger excel document" is set to the 1904 system, but your default for new workbooks is 1900...
    – Madball73
    Commented Mar 6, 2014 at 17:53
  • Could you please include any information about how to change this date configuration in an Excel file? Even if it is covered by the linked page, it would be useful to have the information in your post.
    – Excellll
    Commented Mar 7, 2014 at 14:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .