Managing date formats.

[Raypinn]

I have converted some Apple Numbers spreadsheets to Excel for Mac spreadsheets.
I want link a date from sheet 1 into Sheet 2. and get the following example results.
Sheet 1 shows date in number format: 42702 and displays Date format 28 November 2016
Sheet 2 shows as 29 November 2020

I know the problem relates to the 2 date systems 1900 & 1904 but I can't find a way to convert them.

Hope somebody can help.

Cheers Ray

 

[Foz1980]

You're best off using a formula to add or subtract 1462 (which is the amount of days in 4 years).
For example, if you have a date in cell A1 which is in the 1904 system but want to convert it to 1900 then use formula;

=A1-1462

Does that make sense?

 

[Norie]

How are you linking the date from Sheet1 to Sheet2?

If I put 28 November 2016 in A1 on Sheet1 and put =Sheet1!A2 in A1 on Sheet2 I see 28 November 2016 in A1 on Sheet2.

 

[live_excel]

Hello ,

Use this formula on sheet 2:
=Sheet1!A1+1462

Please let me know if any problem there.

 

[Matt Rogers]

Hi.

Suggestion:

Option Explicit

Sub convert_date()
Dim rngC As Range
Application.EnableEvents = False
For Each rngC In Range("A1:Z100") 'range
If IsDate(rngC.Value) = True Then
rngC.Value = rngC.Value + 1462
End If
Next
Application.EnableEvents = True
End Sub

 

[Raypinn]

You're best off using a formula to add or subtract 1462 (which is the amount of days in 4 years).
For example, if you have a date in cell A1 which is in the 1904 system but want to convert it to 1900 then use formula;

=A1-1462

Does that make sense?

Thanks, will try it

 

[Raypinn]

Thanks, will try it