Date Formatting in Excel 2004

[Andrew Liddle]

Hopefully someone can help. When I copy data with a list of dates from one
Excel file to another the copied data bears no resemblance to the original
date, even when I paste special - formats. For example 10/2/04 becomes
11/2/08. The original Excel file originates from a Windows machine, I don't
know what version of Excel is being used, but I am using version 11.0 for
Mac.

 

[Bernard Rey]

Andrew Liddle wrote :

Hopefully someone can help. When I copy data with a list of dates from one
Excel file to another the copied data bears no resemblance to the original
date, even when I paste special - formats. For example 10/2/04 becomes
11/2/08. The original Excel file originates from a Windows machine, I don't
know what version of Excel is being used, but I am using version 11.0 for
Mac.

In the "Preferences" uncheck the "Calendar starts 1904" button first, in
order to have your workbook settings equivalent to the PC one. But then
you'll have to keep that format (and may encounter the problem again).

You can correct the dates on your sheet if you prefer: you simply have to
take off 1462 (equals 4 years and one day) to the concerned cells. You can
do this with a macro. The lines hereunder convert dates in the selected
cells (or the whole sheet if you want to):

Sub ConvertingDates()
Dim c As Range
For Each c In Selection ' or ActiveSheet.UsedRange
If IsDate(c) Then
With c
MyFormat = .NumberFormat
.Value = .Value - 1462 ' or + 1462 if needed
.NumberFormat = MyFormat
End With
End If
Next c
End Sub

Search the HelpFile for "Date and time" to learn more about the way Excel
(but not only Excel) deals with it.