0The 1900 Date System
In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.
By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.
The 1904 Date System
In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the serial number 34519.
By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.
The Difference Between the Date Systems
Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers, as follows.
Serial number
Date system of July 5, 1998
----------------------------------
1900 date system 35981
1904 date system 34519
The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days bigger than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).
How to handle through VBA
Dim lngRow as long
If this workbook.Date1904 = True Then
For lngRow = 2 To sheet("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
if isdate(sheet("Sheet1")) then
sheet("sheet1").Cells(lngRow, iCol).Value = sheet("sheet1").Cells(lngRow, iCol).Value + 1462
end if
Next lngRow
End if
