Excel dates transformed

  • Thread starter Thread starter jim diehl
  • Start date Start date
J

jim diehl

I've had a problem with pasting cells with dates from one
excel workbook to another. It seems to be platform and
version independent. I've pasted to/from Mac-PC and
different version: PC Office 2000, 97; Mac Office 98.
When rows or cells are copied and pasted into another
worksbook the dates are transformed 1day and 4years in the
future. HELP!
 
A workbook's date system is controlled through Tools, Options, Calculation,
1904 Date System. While selecting this for the PC/Windows based workbook
may solve your copy/paste problem, it will cause any dates currently in the
PC/Windows workbook to go off by 4 years and 1 day.

If the workbooks have different date systems you're always going to have
this problem. One solution is to take, say, the Mac workbook off 1904.
You'd do that by unchecking the 1904 option and then fixing any date cells
by subtracting 4 years and 1 day, or 1462 days.

To make fixing the dates easier you might try this macro:

Sub AdjustMacDatesToWindows()
Dim Cell As Range, WS As Worksheet
On Error GoTo NextSheet
For Each WS In Worksheets
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
If IsDate(Cell.Value) Then Cell.Value = Cell.Value - 1462
Next
NextSheet:
Next
End Sub

If you want to change the Windows workbook to Mac dates then change the
minus sign to a plus.
 
Back
Top