Why do my dates change when I copy them between Excel worksheets?

[rrjohnsonia]

I often copy cell contents from one worksheet and post them into a new one.
When I copy one set of dates and paste them into a new worksheet, the date
falls back one day and to 2001. For example, an original date of May 30, 2005
copies as May 29, 2001.

Our agency's IT guru can't figure it out. I could use some help to end this
frustrating error.

Thanks.

 

[N Harkawat]

Looks like both have different date system
Change them to follow the same date system by going into
TOOLs-OPtions-Calculation
and uncheck 1904 date system from both your Windows machines.
If using Mac I guess leave them checked

 

[Harald Staff]

Hi

One workbook is set to 1900 calendar format (=Windows default calendar). The
other is set to 1904 calendar (Mac). You can do this in the Tools > Options
menu in Windows Excel, and in Excel > Preferences (I think) on a Mac Excel.

The difference between those standards is 1462 days. You can not end this
error in a mixed environment without brute force, but it's easy to convert
from one standard to another by selecting the date cell(s) and run the
proper macro:

Sub Date1900to1904()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If IsDate(Cel.Value) Then
Cel.Value = Cel.Value - 1462
End If
End If
Next
End Sub

Sub Date1904to1900()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If IsDate(Cel.Value) Then
Cel.Value = Cel.Value + 1462
End If
End If
Next
End Sub

HTH. Best wishes Harald

 

[Manish Bajpai]

Hi Sonia,

Try this :

Go to Tools / Options / Calculation and uncheck the box for "1904 date
system" under work book options.

Thanks,

Manish

 

[Peo Sjoblom]

I wouldn't do this right aways since one machine will be 1462 days off, put
1462 days in an empty cell, copy it, select the dates and paste special and
select add, if going the other way subtract


Regards,


Peo Sjoblom

 

[Harald Staff]

Looks like both have different date system
Change them to follow the same date system by going into
TOOLs-OPtions-Calculation
and uncheck 1904 date system from both your Windows machines.
If using Mac I guess leave them checked

This checkbox clicking will shift existing dates 4 years 1 day, just as the
mentioned copying does. Be very careful with this.

This whole thing comes from those computers' date systems. 1 is a day, and
date cells really contain "number of days since day 1". Problem is "day 1
was when ?". Steve says new year 1904, Bill says four years earlier.

HTH. Best wishes Harald

 

[Peo Sjoblom]

This checkbox clicking will shift existing dates 4 years 1 day, just as the
mentioned copying does. Be very careful with this.

This whole thing comes from those computers' date systems. 1 is a day, and
date cells really contain "number of days since day 1". Problem is "day 1
was when ?". Steve says new year 1904, Bill says four years earlier.

Bill even invented a date and started with January 0 1900 <g>


Peo