Cell data changes when copying and pasting

[Mark Christensen]

I've got Excel 2002 SP2. Another person in our office has Excel 2000. She
entered a bunch of data into a spreadsheet for me (the data is dates in
mm/dd/yyyy format). When I copy and paste her data into a new spreadsheet
in one of my existing files, the dates change.

This seems to be the rule of what is happening: when I copy and paste a
cell into my sheet, it adds 1 to the day and 4 to the year. For example, if
she typed 12/18/2003, it pastes it as 12/19/2007; if she typed 4/15/2004, it
pastes as 4/16/2008. We are both baffled by this. There are no formulas
being used, just a basic shreadsheet.

Has anyone seen this before? And does anyone know how to fix it? Thanks in
advance.

Mark

 

[Gord Dibben]

Mark

The workbook was created using the 1904 date system.

(tools>options>calculations)

It is used since it will return negative time while the default returns
####### for negative time
You can fix it easily, in the workbook you can put 1462 in an empty cell,
copy it, in the new workbook select all dates
that changed to earlier dates and do edit>paste special and select add. That
will give you correct dates

***The above was copied(plagiarized?) from a posting by Peo Sjoblom***

Gord Dibben Excel MVP

 

[Mark Christensen]

Got it! Thanks for the help!

Mark

The workbook was created using the 1904 date system.

(tools>options>calculations)

It is used since it will return negative time while the default returns
####### for negative time
You can fix it easily, in the workbook you can put 1462 in an empty cell,
copy it, in the new workbook select all dates
that changed to earlier dates and do edit>paste special and select add. That
will give you correct dates

***The above was copied(plagiarized?) from a posting by Peo Sjoblom***

Gord Dibben Excel MVP

 

[Peo Sjoblom]

FWIW you can run a macro like this if this occurs on a regular basis

Sub Add_Dates()
Application.DisplayAlerts = False
For Each cell In Selection
cell.Value = cell.Value + 1462
cell.NumberFormat = "mm/dd/yyyy"
Next cell
Application.ScreenUpdating = True
End Sub


press Alt + F11, select the personal.xls in the project pane to the left (if
you don't have a personal.xls create one by starting to record a macro and
select personal macro workbook under tools>macro>record and store macro in,
just start and then stop the recording) double click the module in the left
pane and paste in the above either over the created macro or below if you
already have a macro there. Press Alt + Q to close the VBA editor. When you
close excel and are prompted to save personal.xls select yes. To run the
macro select the dates with the mouse, press Alt + F8 to run the macro.