Excel Date Mismatch And Off By One Errors

.xls or .xlsx files in Excel store date values as days elapsed since the beginning of the epoch. So when did the epoch start? Well Excel has two answers for you: 1900 and 1904 depending on your configuration. The history is a bit long but you can dive into specifics here.

Why Excel Epoch Matters?

If you’re trying to read a modern .xls file using an older reader, this could cause your dates to be off by 4 years. Some libraries actually try to fix this by adding 4 years, but miss the leap day correction, which somehow makes it worse.

Imagine loading an xls file that maps date to sales:

  "21/09/2025" => 1
  "22/09/2025" => 2
  "23/09/2025" => 3
  ...

Using a function like this:

  function get_excel_date_by_date($date) {

    $excel_arr = read_file('path_to_file');

    foreach($excel_arr as $row) {
      if($row[0] === $formatted_date)
        return $row
    }

  }

If you mismatch the reading in read_file or use an older library (mine was Spreadsheet_Excel_Reader for php 4!) you’ll end up with exactly one day mismatch in dates. Meaning that for the function get_excel_date_by_date("21/09/2025") you will get 2!

You can check the format of your dates here:

How To Fix It?

Unless you want to replace the reader, the quickest fix is to deal with the off-by-one directly. Some readers can switch between the 1900 and 1904 modes, so check for that first. To see what one serial means in each system, paste it into the Excel date converter.

Better yet, get out of Excel if you can. CSV works miracles: it’s plain text, has no epoch to argue about, and doesn’t need a single dependency to process. Unless the other side is a bank: then it’s fixed-width ASCII.