Skip to contents

Revert Excel date serials across a data frame

Source: R/restore.R
fix_serial_columns.Rd

Applies restore_day_month() to the columns that look dominated by Excel serials, leaving the rest of the frame alone. Column selection is a heuristic, controlled by pmin: it is the price of working from imported values. If the original workbook is available, unexcel_xlsx() selects columns from the file's own date formatting instead, and excel_date_columns() reports that selection for a frame imported by any other reader.

Usage

fix_serial_columns(
  df,
  pmin = 0.8,
  cols = NULL,
  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

df

A data frame.

pmin

Minimum fraction of a column's values that must be in-range whole numbers before the column is treated as serials.

cols

Optional column names or indices to restrict the operation to. Supplying this turns off the pmin scan, so nothing is selected by guesswork; pair it with excel_date_columns() for a fully determined result.

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

The data frame, with the selected columns restored.

See also

unexcel_xlsx(), excel_date_columns().

Examples

df <- data.frame(a = c(45746, 45823), b = c(1.2, 3.4))
fix_serial_columns(df)
#>      a   b
#> 1 30.3 1.2
#> 2 15.6 3.4

# Name the columns yourself and nothing is inferred
fix_serial_columns(df, cols = "a")
#>      a   b
#> 1 30.3 1.2
#> 2 15.6 3.4