How to Quickly Convert 1904 Dates to the 1900 Date System in Excel

by

Introduction

Microsoft Excel’s handling of dates is a critical feature for accurate data management. However, discrepancies between the 1900 and 1904 date systems can lead to confusion and errors. This comprehensive guide will help you understand these systems and provide step-by-step instructions for adjusting dates correctly.

Why are there 1904 and 1900 date systems in Excel?

The 1904 date system was used by default in Excel for Mac. Historically, this was because the earlier versions of Excel for Mac needed to be compatible with Macintosh System Software, which used the 1904 date system. The 1900 date system was designed to be compatible with Lotus 1-2-3, a popular spreadsheet program during the early days of Excel. However, in the latest versions of Excel, both for Mac and Windows, the default date system is the 1900 system, although users can switch to the 1904 system if required.

How to check the current date system in Excel?

To check the current date system in Excel (whether it’s using the 1900 or 1904 date system), follow these steps:

  1. Click on ‘File’ in the top left corner of Excel.
  2. Go to ‘Options’ to open the Excel Options dialog box.
  3. In the Excel Options, select ‘Advanced’.
  4. Scroll down to the ‘When calculating this workbook’ section.
  5. Look for the option that says ‘Use 1904 date system’. If this box is checked, Excel is using the 1904 date system. If it’s unchecked, it’s using the 1900 date system.

The Challenge

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.

1904 Dates in Excel

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.

1904 Date Setting

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.

1904 Dates in 1900 Spreadsheet

Convert 1904 Dates to 1900 Dates using VBA Macro

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.

VBA Code to Adjust 1904 Dates

All dates have been converted and the code is no longer needed.