Revert Excel date serials in an already-imported vector

Restores the day.month values a spreadsheet auto-converted to dates, working from the imported numbers alone. Use this when the original .xlsx is gone – a CSV export, a legacy .xls, a data frame handed over by a collaborator. When the workbook is available, use unexcel_xlsx() instead: it reads the date system and the date formatting out of the file, so it needs none of the guardrails below.

Usage

restore_day_month(
  x,
  low_serial = 20000,
  high_serial = 65000,
  year_window = 1990:2035,
  date_system = c("1900", "1904"),
  order = c("dm", "md"),
  output = c("numeric", "character"),
  origin_mode = NULL,
  ref_date = Sys.Date()
)

Arguments

x

A numeric, integer, character or Date vector.

low_serial, high_serial

Bounds, inclusive, on what counts as a plausible serial. The defaults span roughly 1954 to 2078.

year_window

Years that a converted serial may resolve to. A serial landing outside the window is left alone.

date_system

"1900" or "1904"; see excel_date_system().

order

"dm" or "md"; see serial_to_day_month().

output

"numeric" or "character"; see serial_to_day_month(). "character" preserves a trailing zero, so a value typed as 3.10 survives as "3.10" rather than 3.1.

origin_mode

Deprecated. "1900" and "1904" are passed to date_system. "auto" re-enables the 0.1.0 behaviour of picking the date system by comparing candidate dates against ref_date, with a warning: that comparison is a heuristic and can shift every restored value by four years and a day.

ref_date

Reference date used only by origin_mode = "auto".

Value

A vector the same length as x: numeric when every value can be represented as a number, character otherwise.

What is inferred, and what is not

Given a serial, a date system and a field order the conversion is exact (see serial_to_day_month()). Without the file, one thing cannot be known: which numbers are serials. That is what low_serial, high_serial and year_window decide, and they are deliberately conservative – a value is converted only if it is a whole number, falls inside the serial range, and resolves to a year inside the window. Everything else is returned untouched.

The other two facts should be supplied rather than inferred. date_system defaults to "1900", which is what every current Excel writes; read the true value with excel_date_system() if you still have the workbook. order defaults to "dm", the locale convention under which 30.3 is turned into a date in the first place.

See also

unexcel_xlsx() for the file-driven path, fix_serial_columns() for whole data frames.

Examples

# 45746 is 2025-03-30; 12.5 is not a whole number, so it is left alone
restore_day_month(c(45746, 12.5, 45823))
#> [1] 30.3 12.5 15.6

# Month-first workbooks
restore_day_month(45746, order = "md")
#> [1] 3.3

# Take the date system from the file rather than the default
path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")
restore_day_month(45746, date_system = excel_date_system(path))
#> [1] 30.3