Excel date systems and number formats

library(unexcel)

This vignette is the reference behind unexcel’s decisions: what a serial is, why there are two of them, and how a workbook records which cells are dates.

Serials

A spreadsheet stores a date as the number of days since an origin. The stored value is a plain number; the calendar appearance comes entirely from the cell’s number format. Change the format to General and the date shows its serial.

That is why date auto-conversion is destructive in a specific way: the typed text 30.3 is gone, replaced by 45746, and only the formatting records that the number is meant to be read as a date.

Two origins

There are two systems in circulation.

The 1900 system is what Excel writes on every current platform. Serial 1 is 1900-01-01. Excel also carries 1900-02-29 — a date that never existed, retained for compatibility with Lotus 1-2-3, which had the bug first. R has no such day, so an origin of 1899-12-30 absorbs the extra day and reproduces what Excel displays for every serial from 61 onward:

excel_origin("1900")
#> [1] "1899-12-30"
as.Date(45746, origin = excel_origin("1900"))
#> [1] "2025-03-30"

The 1904 system came from early Macintosh Excel, which sidestepped the Lotus bug by starting later. Serial 0 is 1904-01-01. Workbooks created in it still circulate, and the option survives in current Excel:

excel_origin("1904")
#> [1] "1904-01-01"

The two are 1462 days apart — four years and a day:

as.numeric(excel_origin("1904") - excel_origin("1900"))
#> [1] 1462

That gap is the reason to read the date system rather than guess it. Both readings of a serial are plausible dates in living memory, so no test of reasonableness separates them:

serial_to_day_month(44284, date_system = "1900")
#> [1] 29.3
serial_to_day_month(44284, date_system = "1904")
#> [1] 30.3

Both answers look entirely ordinary. Only the workbook knows which is right, and it says so in the date1904 attribute of xl/workbook.xml:

excel_date_system(system.file("extdata", "legacy-1904.xlsx", package = "unexcel"))
#> [1] "1904"

Number formats

xl/styles.xml holds a table of cell formats (cellXfs); each cell’s s attribute indexes into it, and each entry names a number format id. Ids 14 to 22 and 45 to 47 are Excel’s built-in date and time formats; ids from 164 up are custom, and the workbook stores their format codes alongside.

unexcel treats a cell as a date when its format is one of the built-in date formats — the time-only ones, 18 to 21 and 45 to 47, are excluded, since a duration is not a mistyped day.month — or when a custom format code contains date tokens. Quoted literals, escaped characters and bracketed sections ([Red], [<100], [$-409]) are stripped first, so a currency format reading "day "0.0 is not mistaken for a date, and only the positive-number section of a multi-part code is considered.

The remaining ambiguity is m, which means months in d/m/yyyy and minutes in h:mm. It is read as a month unless hours or seconds appear alongside.

path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")
excel_date_columns(path)
#>   col  name n_date n_values prop_date field_order format_code
#> 1   2  dose      5        5         1          dm    d/m/yyyy
#> 2   5 visit      5        5         1          md    mm-dd-yy

The same format code also settles the field order. A d/m/yyyy column restores day-first, an mm-dd-yy column month-first, and neither has to be assumed:

unexcel_xlsx(path)[, c("dose", "visit")]
#>    dose visit
#> 1 30.30  1.70
#> 2 15.60  2.11
#> 3  3.10  3.40
#> 4  1.12  4.90
#> 5 28.20  5.60

What is still lost

Reading the file recovers the number that was typed, not the keystrokes.

A value typed as 3.10 becomes the 3rd of October, which restores to the number 3.1. The trailing zero is not recoverable numerically — the file records a day and a month, and 10 is the month either way. output = "character" keeps the printed form instead:

unexcel_xlsx(path, output = "character")$dose
#> [1] "30.3" "15.6" "3.10" "1.12" "28.2"

A value that Excel converted to a date and that carried more than two components — 30.3.2025 — leaves the day.month idea behind entirely; the restored value is still 30.3, and the year is discarded by design.

Finally, if a column was auto-converted and then re-formatted as General by a later editor, the workbook no longer describes it as a date. The serial is still there, but the evidence is not, and restore_day_month() with its guardrails is all that remains.

Further reading