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?
1I 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 – 2014-03-06T17:37:23.557
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 – 2014-03-06T17:50:12.723
See answer below. The MS KB is quite clear I think. – Madball73 – 2014-03-06T17:58:44.170