Mixing dates from a 1900 date system and a 1904 date system

[Robin]

So I'm compiling a number of spreadsheets created by different people
on different systems (i.e. Mac and Windows) and I've run into a date
issue.

I'm on a Mac and because Windows uses 1900 as it's start date for
calculating dates and Mac uses 1904, when I copy data from a
spreadsheet crrated on a Windows machine the dates get screwed up. You
can change whether or not the spreadsheet you're using is based on
1900 or 1904 (a checkbox under preferences), but that only changes the
way the date is displayed. The actual number that Excel uses to
calculate the date stays the same, so even if I swapped over to a
Windows box to compile the spreadsheets I'd get the same issue.

Surely somewhere there's a way to take the Excel date number used in a
1900 based system and convert it into the equivalent date number on a
1904 based system, but I've not been able to find it. Anyone know how
to do this?

 

[JE McGimpsey]

Surely somewhere there's a way to take the Excel date number used in a
1900 based system and convert it into the equivalent date number on a
1904 based system, but I've not been able to find it. Anyone know how
to do this?

A manual method:

1900 system dates are larger than 1904 system dates by 1462, so

1) enter 1462 in an empty cell
2) copy the cell
3) select the dates to convert
4) Choose Edit/Paste Special, selecting the Values and
Add (1904->1900) or Subtract (1900->1904) radio buttons.

I use these macros:

Public Sub ConvertDates_1900To1904()
Dim rCell As Range
For Each rCell In Selection
With rCell
If IsDate(.Text) Then _
.Value = .Value - 1462
End With
Next rCell
End Sub

Public Sub ConvertDates_1904To1900()
Dim rCell As Range
For Each rCell In Selection
With rCell
If IsDate(.Text) Then _
.Value = .Value + 1462
End With
Next rCell
End Sub