An associate of mine “helped” me out once by putting a worksheet together for data entry. After the worksheet had several days of user input, I found out the workbook was created on a Mac with an older version of Excel for Mac (2004) that defaulted to the 1904 date system.
Since the worksheet in question would be used entirely by Windows users, I decided to change to the 1900 date system to avoid any problems. Little did I know that by changing that simple date system setting would change the dates by 1462 days.
That’s because first day of the 1900 date system starts on 1 Jan, 1900 and the first day of the 1904 date system starts on 2 Jan, 1904.
Here’s an example of the dates entered with the 1904 date system. I’ve added a column that shows the underlying date serial number.
To change the Date System setting choose File > Options then click the Advanced tab and scroll down to un-check the box for Use 1904 date system.
All dates entered into the worksheet will be altered to fit the 1900 date system and give you totally different dates, which can be confusing. Notice how the dates have changed in the picture below, but the underlying date serial number is the same.
To change this I wrote a simple VBA macro to convert all the date values. Simply select all the date values then run the macro.
Sub Correct1904Dates()
For Each cell In Selection
cell.Value = cell.Value + 1462
Next cell
End Sub
It occurred to me that a macro like this is not needed for this temporary measure. The trick is to use the Immediate window of the VBA Editor and write the code on one line with a colon between the lines.
Select the date values and open the VBA Editor (Alt+F11), then show the Immediate window (Ctrl+G) and type the following code.
All dates have been converted and the code is no longer needed.