A strange date problem

[Tom Brown]

I have a spreadsheet with many dates in it. I Select All and then press
Ctrl-C to copy it to the clipboard. Then, I go to another Workbook and
click into the Sheet 4 and click on the same box in the upper left corner
and press Ctrl-V to paste all the data from the original worksheet into the
new one.

The problem is that ALL the dates are changed in the new spreadsheet. For
example the date in cell b:6 in the first sheet is 10/1/05 and when it is
pasted into the new sheet it is 9/31/01. All the other dates are about 4
years off.

Does anyone have any clue what this is all about?

TIA,

Tom

 

[Peo Sjoblom]

Yes, the workbook with the 10/01/05 is using the 1904 date system (for
Macintosh) and the workbook that returns 09/30/05 is windows excel default,
to convert either subtract or add 1462 days by putting 1462 in an empty
cell, copy it, select the dates that are "off" and (in your case) do
edit>paste special and select add, if you don't want to reformat the cell(s)
again format the cell you put 1462 in as date that way it won't change the
format, otherwise just paste special and afterwards do format>cells>number
and select the date format

--
Regards,

Peo Sjoblom

(No private emails please)

 

[JE McGimpsey]

Yup. The workbook you're copying from is set to the 1904 Date System,
and your destination workbook is set to the 1900 Date System.

The date systems are 4 years and 1 day offset (the 1 day because the
1900 system incorrectly includes a phantom 29 February 1900).

Change both systems to the same (Tools/Options/Calculation), or add the
constant 1462 to the dates in your destination workbook.

 

[Gord Dibben]

Tom

One of the workbooks was saved using the Tools>Options>Calculation>1904 date
system.

The other is using the standard Excel date system.

Looks like source wookbook is using 1904 system.

I would uncheck that option in source book unless there is a compelling reason
to leave it on 1904 date system..

This will change the dates by 4 years and a day.

To resolve this, enter 1462 in an empty cell and copy it then select the dates
and paste special>add>OK>Esc.

Re-format to your preferred date format.

Now copy over to the target workbook.


Gord Dibben Excel MVP

 

[Tom Brown]

Yep, the original speadsheet was created on a Mac.

Thanks gentlemen!

Tom