Skip to contents

unexcel

Type 30.3 into a spreadsheet in a day-first locale and it is not stored as the number 30.3. The application decides you meant the 30th of March and stores a day count instead. Import the file and the column arrives as 45746.

unexcel gets the typed numbers back — and gets them back from the file rather than from a guess. An .xlsx workbook states its own date system and records which cells are formatted as dates and in which field order, so the values to repair are read out of the file, not inferred from their magnitude.

Installation

Install the released version from CRAN:

install.packages("unexcel")

Or the development version from GitHub:

# install.packages("remotes")
remotes::install_github("drhrf/unexcel")

Quick start

The example workbook holds five doses typed as 30.3, 15.6, 3.10, 1.12 and 28.2. This is what the spreadsheet stored:

library(unexcel)

path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")

unexcel_xlsx(path, restore = FALSE)
#>   sample       dose weight reading      visit
#> 1     S1 2025-03-30   70.5   45000 2025-01-07
#> 2     S2 2025-06-15   62.1   45120 2025-02-11
#> 3     S3 2025-10-03   80.0   45230 2025-03-04
#> 4     S4 2025-12-01   55.4   45310 2025-04-09
#> 5     S5 2025-02-28   91.2   45400 2025-05-06

And this is what was typed:

unexcel_xlsx(path)
#>   sample  dose weight reading visit
#> 1     S1 30.30   70.5   45000  1.70
#> 2     S2 15.60   62.1   45120  2.11
#> 3     S3  3.10   80.0   45230  3.40
#> 4     S4  1.12   55.4   45310  4.90
#> 5     S5 28.20   91.2   45400  5.60

No guessing

Look at reading. Its values sit squarely inside the range of plausible date serials — closer to a real serial than some of the dose values are. It was left alone anyway, because the workbook does not format it as a date. And visit restored month-first, because its format code is mm-dd-yy.

Neither decision is available from the imported numbers. Both are read from the file:

excel_date_system(path)   # from xl/workbook.xml, not inferred from magnitude
#> [1] "1900"
excel_date_columns(path)  # from xl/styles.xml, not inferred from range
#>   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

excel_date_cells() is the audit trail — every value that would change, with the serial it came from and the format code that justifies changing it:

head(excel_date_cells(path), 4)
#>   ref row col serial       date num_fmt_id format_code field_order day_month
#> 1  B2   2   2  45746 2025-03-30        164    d/m/yyyy          dm     30.30
#> 2  E2   2   5  45664 2025-01-07         14    mm-dd-yy          md      1.70
#> 3  B3   3   2  45823 2025-06-15        164    d/m/yyyy          dm     15.60
#> 4  E3   3   5  45699 2025-02-11         14    mm-dd-yy          md      2.11

Why the date system matters

Excel has two date origins, 1462 days apart. Both readings of a serial land on an ordinary date 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

Only the workbook knows which is right, and it says so.

When the workbook is gone

CSV exports and inherited data frames carry no formatting. restore_day_month() works from the values alone, behind three deliberately conservative guardrails — whole number, plausible serial range, resolved year inside a window:

restore_day_month(c(45746, 12.5, 45823))
#> [1] 30.3 12.5 15.6

df <- data.frame(dose = c(45746, 45823), weight = c(70.5, 62.1))
fix_serial_columns(df)
#>   dose weight
#> 1 30.3   70.5
#> 2 15.6   62.1

Anything you do know should be supplied rather than left to a default:

restore_day_month(44284, date_system = "1904")
#> [1] 30.3
restore_day_month(45746, order = "md")
#> [1] 3.3
fix_serial_columns(df, cols = "dose")   # name the columns, skip the scan
#>   dose weight
#> 1 30.3   70.5
#> 2 15.6   62.1

Why unexcel?

  • Evidence, not heuristics — the date system, the date-formatted cells and the day/month order all come from the workbook itself.
  • Ordinary numbers stay ordinary — a value is restored because it is formatted as a date, never because it is large enough to look like one.
  • Auditable — excel_date_cells() shows every change with the format code behind it, before anything is changed.
  • Handles the messy case too — restore_day_month() and fix_serial_columns() still work when only the imported values survive, and say plainly what they are assuming.
  • Both date systems — 1900 and 1904, read from date1904 rather than guessed from how recent the dates look.
  • Light — one dependency (xml2) beyond base R.

Learn more