Importing Excel Dates Correctly Into a Date Column

The date column is the one that goes wrong most often in an Excel load, because it is the one where the file and the person disagree about what is stored. The person sees dates. The file holds numbers, strings, or both in the same column, and the database wants a single date type. A loader that trusts a library's guess ends up with half the dates correct and the rest as nulls or garbage. This post shows a procedure that makes every date explicit: stage as text, classify, convert by class, reject the leftovers. It belongs to the cluster on getting Excel data into a database, and it builds on how Excel stores dates as serial numbers.

Three kinds of values in one column

Consider a column that was filled by several people over a year. Staged as text, it holds:

If you read the workbook with a library that applies number formats, the first two arrive as dates already (2024-01-01 00:00:00), which is convenient and hides the distinction; the text cells stay text. Reading raw with dtype=str preserves the serial numbers, and I prefer it here because it shows exactly what the file holds. In either case the column is a mix, and the right approach is to treat each kind on its own terms.

Classify in the stage

Load the column as text into the stage (staging Excel data in a table before cleaning it), then classify with patterns. In PostgreSQL:

SELECT
  CASE
    WHEN ordered IS NULL OR ordered = ''                    THEN 'empty'
    WHEN ordered ~ '^\d{4,6}(\.\d+)?$'                      THEN 'serial'
    WHEN ordered ~ '^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}:\d{2})?$' THEN 'iso'
    WHEN ordered ~ '^\d{1,2}/\d{1,2}/\d{4}$'                THEN 'slash'
    ELSE 'other'
  END AS kind,
  count(*) AS n
FROM stage_orders
WHERE batch_id = 42
GROUP BY 1
ORDER BY n DESC;

For a small example of 100 rows the result might read:

 kind  |  n
-------+----
 serial | 71
 iso    | 18
 slash  |  8
 empty  |  2
 other  |  1

This tells you what you are dealing with before you convert a single value. Seventy-one serial numbers take one rule, 18 ISO dates need only a cast, 8 slash dates have the ambiguity problem, 2 are empty, and 1 is something else. The serial pattern allows four to six digits so that it does not swallow a four-digit year such as 2024 typed alone, but be aware that a bare 2024 would also match it; a serial of 2024 is the date 1905-07-17, which is almost never right for business data. Tighten the range to what is plausible for your data, for example 36000 to 60000 (years 1998 to 2064).

Convert each class

Write one conversion per class and combine them in a CASE:

SELECT source_row,
  CASE
    WHEN ordered ~ '^\d{5}(\.\d+)?$'
      THEN DATE '1899-12-30' + floor(ordered::numeric)::int
    WHEN ordered ~ '^\d{4}-\d{2}-\d{2}'
      THEN left(ordered, 10)::date
    WHEN ordered ~ '^\d{1,2}/\d{1,2}/\d{4}$'
      THEN to_date(ordered, 'DD/MM/YYYY')
  END AS ordered_on
FROM stage_orders
WHERE batch_id = 42;

The serial branch adds the whole number of days to 30 December 1899, which is correct for dates after February 1900 (converting Excel serial dates to real dates in SQL). floor drops the time fraction; if you need the time, build a timestamp as TIMESTAMP '1899-12-30' + ordered::numeric * INTERVAL '1 day'. The ISO branch keeps the first ten characters and casts. The slash branch uses to_date with a stated pattern.

The slash dates need a decision, not a guess. 05/01/2024 is 5 January in a day-first locale and 1 May in a month-first one, and no pattern tells you which. Look for evidence in the data: if any value in the column has a first number above 12 (25/01/2024), the column is day-first; if any has a second number above 12, it is month-first. A query can test it:

SELECT bool_or(split_part(ordered, '/', 1)::int > 12) AS has_day_first_proof,
       bool_or(split_part(ordered, '/', 2)::int > 12) AS has_month_first_proof
FROM stage_orders
WHERE batch_id = 42 AND ordered ~ '^\d{1,2}/\d{1,2}/\d{4}$';

When both are false, the column is ambiguous and you must ask the sender. Do not pick one because it works on your machine. A date that is wrong by months and valid in the calendar is the worst kind of bad data, since nothing flags it.

The other class and the unmatched empty should not be guessed at. Send the other rows to a rejects table:

INSERT INTO rejected_rows (batch_id, source_row, column_name, raw_value, reason)
SELECT batch_id, source_row, 'ordered', ordered, 'unrecognized date format'
FROM stage_orders
WHERE batch_id = 42
  AND ordered <> ''
  AND ordered !~ '^\d{5}(\.\d+)?$'
  AND ordered !~ '^\d{4}-\d{2}-\d{2}'
  AND ordered !~ '^\d{1,2}/\d{1,2}/\d{4}$';

Now the person who owns the file can see exactly which rows to fix, by row number.

Check the date system

Every serial conversion above assumes the default 1900 date system. A workbook created in very old Excel for Mac may use the 1904 system, in which the same serial number means a date 1,462 days later (about four years). The flag is in xl/workbook.xml as date1904="1" on the workbookPr element, and openpyxl exposes it:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx", read_only=True)
print(wb.epoch)

1900-01-01 00:00:00

For a 1904 workbook wb.epoch is 1904-01-01. Record it per batch and add 1462 days to every serial when it is set, or convert with the right epoch (Mac and Windows date systems in Excel). The check is cheap, and a file with the 1904 flag is rare enough that nobody remembers to look until a report shows orders four years in the past.

Do the conversion in pandas if you prefer

If your loader is already in Python, the same classification works there:

import pandas as pd

s = df["Date"]
serial = pd.to_numeric(s, errors="coerce")
as_serial = pd.to_datetime(serial, unit="D", origin="1899-12-30")
as_iso = pd.to_datetime(s.where(serial.isna()), format="%Y-%m-%d", errors="coerce")
dates = as_serial.fillna(as_iso)
bad = df[dates.isna() & (s != "")]
print(len(bad), "rows could not be read")

to_numeric(errors="coerce") turns non-numbers into NaN, to_datetime(..., unit="D", origin=...) interprets the numbers as days from the epoch, and the second parse handles ISO strings that were not numeric. The bad frame holds what neither rule explained. You see the same classes as in SQL, in code form (converting Excel serial dates in Python). Choose one place to convert and keep it there; converting in both Python and SQL doubles the chance of a mismatch.

Time zones and times

An Excel date-time carries no zone. A timestamp that the sender typed in local time stays local time in the file. Decide what the target column means: a timestamp without time zone that holds local time and a note saying which zone, or a timestamptz with the zone applied during conversion (AT TIME ZONE 'Europe/Oslo'). Storing local times as if they were UTC shifts every value by the offset, and daylight saving makes the shift change twice a year. If the column is a pure date, there is no zone problem, which is one more reason to use a date type when the time of day is not meaningful.

Time-only cells are fractions of a day (0.5 is noon). Convert them with TIME '00:00' + fraction * INTERVAL '1 day', and keep them in a time column, not combined with a date you invented.

When converting Excel dates by rule is not enough

The ambiguity cannot be resolved. If a column of day-or-month-first dates has no value that proves its order, ask the sender. No rule can recover the information.

The dates are free text. Entries like "early March" or "Q2" are not dates. Keep them as text in a separate column, or define what each phrase means with the business.

The workbook mixes date systems. Combined files from different sources may have some sheets in 1904 and some in 1900. Treat each sheet's flag separately.

The date is a deadline relative to something else. "Net 30" is a rule, not a date. Model it as a number of days.

The functions used are documented in PostgreSQL's pages on date and time functions and data type formatting functions. Microsoft explains the epoch difference in its note on the 1900 and 1904 date systems. For a quick look at a small extract as JSON, use the CSV to JSON converter.