---
title: "Excel date systems and number formats"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Excel date systems and number formats}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
```

```{r setup}
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:

```{r}
excel_origin("1900")
as.Date(45746, origin = excel_origin("1900"))
```

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:

```{r}
excel_origin("1904")
```

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

```{r}
as.numeric(excel_origin("1904") - excel_origin("1900"))
```

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:

```{r}
serial_to_day_month(44284, date_system = "1900")
serial_to_day_month(44284, date_system = "1904")
```

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

```{r}
excel_date_system(system.file("extdata", "legacy-1904.xlsx", package = "unexcel"))
```

## 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.

```{r}
path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")
excel_date_columns(path)
```

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:

```{r}
unexcel_xlsx(path)[, c("dose", "visit")]
```

## 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:

```{r}
unexcel_xlsx(path, output = "character")$dose
```

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

- ECMA-376 Part 1, §18.8.30 (`numFmt`) — the number format grammar.
- Microsoft, "Differences between the 1900 and the 1904 date system in Excel".
- Microsoft, "Excel incorrectly assumes that the year 1900 is a leap year".