Same number displaying two different dates

[unclemuffin]

I have two spreadsheets open. One of them has a lot of shipping data
with columns for address, shipment type, dates, etc. I copied this
data to another spreadsheet the date values copy the same but the date
is not displaying corrcectly.

For example on the sheet from which I am copying the date value is
39811.00 and is formatted *mm/dd/yyyy and it shows 12/29/2008. When I
copy and paste values to the new sheet the number 39811.00 formatted
the same way as above is displaying 12/30/2012. The only difference
between the data in the worksheet from which I am copying and the data
on the destination worksheet is that the destination worksheet data is
formatted as a table. I have this same problem no matter how I format
the number in the new worksheet.

So why would two different worksheets open at the same time display
two different dates for the same date number?

 

[Niek Otten]

One version has the 1904 date system enabled.
Tools>Options>Calculation tab
If there is no specific reason for using the 1904 date system, like
compatibility with the Mac or displaying negative times, don't use it.

 

[Pete_UK]

Excel has two different date formats - one based on 1900 as the
reference year, the other using 1904 as the reference year. Click on
Tools | Options | Calculation tab, and uncheck the 1904 date system in
the second workbook.

Hope this helps.

Pete

 

[Dave Peterson]

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system (in xl2003 menus))

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates.
Edit|PasteSpecial|click Add and values
(or subtract depending on which workbook you're changing)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.