Why 1904 date

[Eng Khoon]

Just out of curiosity, why does Mac Excel use a 1904 date instead of 1900?

 

[Bernard REY]

Eng Khoon wrote :

Just out of curiosity, why does Mac Excel use a 1904 date instead of 1900?

In fact, the question should be asked differently: Originally, Excel was
designed on/for the Macintosh. So it used the Macintosh time/date standard.
When it was re-designed for the PC, it was modified in order to be
compatible with Lotus 1-2-3 (the PC leading Spreadsheet soft at the time)
and so it turned to 1900 (and thus including a Lotus bug: 1900 being a
non-leap year, but considered as a leap-year in Lotus, so in Excel too).

Why 1904:

http://developer.apple.com/qa/ops/ops23.html

Differences in Excel:

http://support.microsoft.com:80/support/kb/articles/q180/1/62.asp

 

[Eng Khoon]

Thank you for explaining and for the links to those articles. Wow.

Just one more question if you don¹t mind. Does this mean that when I open a
worksheet created under Windows, the 1904 date option will automatically be
unchecked and that a Windows user opening my worksheet will see 1904 date
checked?

Thanks a bunch again.


regards
engkhoon

 

[Bernard REY]

Eng Khoon wrote :

Just one more question if you don¹t mind. Does this mean that when I open a
worksheet created under Windows, the 1904 date option will automatically be
unchecked and that a Windows user opening my worksheet will see 1904 date
checked?

That's it. But remember this is a "WorkBook" setting. This means that if you
copy/paste the date from a sheet created on a PC to a sheet on a Mac-created
Workbook, the date will be different:

The serial number corresponding to today (11/22/2003) on a Mac is 36485
On a PC, this value will be the serial number for 11/21/1999. And when you
copy/paste from one WorkBook to the other, it's the serial number you are
copying, along with the format. So it's important to take care of this.

Of course, I mentioned here PC and Mac with the corresponding standard
settings. Hope it is clear enough...

 

[Eng Khoon]

Yes, it's very clear to me now. Thank you all so much, Bernard.


regards
engkhoon

 

[Michel Gaboly]

Hi,

I recently found a tip to copy dates from a workbook to another which have
different settings : one 1900 based and the other 1904 based

If Ref represents the cell with a date in the first workbook, and the second
workbook is the active one, with :

ActiveCell.Value = Ref

you get the classic 1462 days difference


But with

ActiveCell.Value = Ref - 0

everything works fine (no more 1462 days difference).


I first noticed that if you report the date in the workbook containing the macro,

ThisWorkbook.ActiveSheet.ActiveCell = Ref

everything always works fine : you never get any difference even if the two
workbooks, even if they have 2 different date systems (1900 and 1904). This
means that VBA is able to recognize the date.

I suppose than with

ActiveCell.Value = Ref - 0

or

ActiveCell.Value = Ref + 0

the date in translated in an internal value, in the workbook containing the code.


You can easily verify the tip is working :

1 - Create a new workbook
2 - Check the option "Calendar from 1904" ? ("Calendrier depuis 1904"
in french).
3 - Enter a date in a cell
4 - Select this cell
5 - Run this code :

Sub ReportDate()
Dim Ref As Range, Dest1 As Workbook, Dest2 As Workbook
Set Ref = ActiveCell
' Add a 1904 based workbook
Workbooks.Add
Set Dest1 = ActiveWorkbook
Dest1.Date1904 = True
' Add a 1900 based workbook
Workbooks.Add
Set Dest2 = ActiveWorkbook
Dest2.Date1904 = False
' Report of the date on the 2 workbooks
Report Dest1, Ref
Report Dest2, Ref
End Sub

Sub Report(w As Workbook, Ref As Range)
With w.ActiveSheet.Range("A1")
Range(.Offset(0), .Offset(1)).NumberFormat = "dddd d mmmm yy"
.ColumnWidth = 20
.Value = Ref - 0
.Offset(1) = Ref
End With
End Sub

This code use a range object, "Ref" to refer to the ActiveCell of the ActiveWorkbook
Then 2 workbooks are created, one 1904 based, the other one1900 based.
After that, the date from "Ref" is reported twice in the 2 workbooks :

in A1 with .Value = Ref - 0
in A2 with .Offset(1) = Ref

In the 1900 based workbook, the value in the 2 cells is the same one, corresponding
to the date entered in the "Ref" Range.

In the 1904 based workbook, the value in the 2 cells differs : the right one in A1
and the date plus 1462 days in A2 :

Using Ref, we get a difference
Using Ref - 0, no more difference ;-))


Eng Khoon a wrote :