Dates stored as serial numbers
A date appears as a plain number, such as 45292, because it was exported as a count of days or seconds instead of as a calendar date.
What it looks like
Before
| order_date | how it was read |
|---|---|
| 45292 | · |
| 45337.75 | · |
| 1704067200 | · |
| 2024-04-01 | · |
After
| order_date | how it was read |
|---|---|
| 2024-01-01 | spreadsheet day count |
| 2024-02-15 18:00 | spreadsheet day count, the fraction is the time of day |
| 2024-01-01 | seconds since 1970, in universal time |
| 2024-04-01 | already a date |
How it happens
- Spreadsheets store a date as a number of days from a starting date and only display it as a date. Exporting or reformatting the cell exposes the number.
- Databases and programs often store time as seconds or milliseconds since the start of 1970.
- A column was pasted as values into a cell formatted as a number or as text.
What it breaks
- The column is treated as a quantity, so reports may sum or average it.
- Date filters and groupings by month skip the rows entirely.
- Converting with the wrong starting point gives a valid date that is years away from the true one.
How to spot it
- Look for whole numbers of similar size in a column whose name says date. Recent dates as spreadsheet day counts are five digits long.
- Look for ten-digit or thirteen-digit numbers, which suggest seconds or milliseconds since 1970.
- Check for a decimal part. In a day count, the fraction is the time of day.
How to fix it
- Identify which counting system the source uses before converting. Spreadsheets have more than one starting date, and the two differ by about four years.
- Convert a few values by hand and compare them against dates you already know to be right.
- Convert the column and write it year first. Keep any time of day that the fraction carried.
- Leave alone values that fall outside the plausible range for the column. They may be real numbers that ended up in the wrong column.
- Mixed date formatsOne column holds dates written in several different styles.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Missing or mixed time zonesTimestamps in one column were recorded in different time zones, or carry no zone at all.
TermsSerial date, Data type, Type coercion, Spreadsheet