Dates shifted when copying sheet to new workbook

[spacecityguy1]

Hi all,

I've a mystery I had try to solve for some time and can't figure out where to start. I inherited a very simple single-sheet Excel file with names and dates in it. When I make a copy of this sheet to a new workbook, all dates are shifted by roughly 4 years (e.g. 1/28/2014 becomes 1/27/2010). I tried to insert a couple blank sheets and move the data sheet (instead of copying it) to a new workbook to no avail. I have examined the date field and found the issue in the source file. When I change the format of the cell with 1/28/2014 in it to "general", I get 40205 (incorrect) instead of 41667 (correct). And when I change the cell format back to date, I get 1/28/2014 once again. If I change the cell value to 41667, I get 1/29/2018. Anyone has any idea what I can do to fix this problem?

Thanks.

[TMS]

Duplicate thread: http://www.excelforum.com/excel-prog...e-reports.html

[TMS]

You have 1904 date settings in one or other of the workbooks. Look in Excel Options | Advanced | Using 1904 Date System


Regards, TMS

[spacecityguy1]

Thanks for pointing it out! I thought I knew quite a bit about Excel

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.