janitor excel_numeric_to_date(): Excel Serial to R Date

janitor::excel_numeric_to_date() converts Excel serial date numbers (like 44197 for 2021-01-01) into native R Date objects. It handles the modern Windows system and the legacy Mac 1904 system, and returns POSIXct with time when the serial carries a fractional component.

excel_numeric_to_date(44197)                                          # single serial to Date
excel_numeric_to_date(c(44197, 44562, 44927))                         # vector of serials
excel_numeric_to_date(44197.5, include_time = TRUE)                   # serial with time
excel_numeric_to_date(34001, date_system = "modern")                  # default Windows system
excel_numeric_to_date(32568, date_system = "mac pre-2011")            # legacy Mac files
excel_numeric_to_date(44197, include_time = TRUE, tz = "America/New_York")   # custom tz
df |> mutate(date = excel_numeric_to_date(date_num))                  # inside a pipe

What excel_numeric_to_date() does

excel_numeric_to_date() turns an Excel serial date into a real R Date. Excel stores dates as the count of days since 1899-12-30 (modern Windows system), so 44197 means 2021-01-01. When you import via read.csv(), or when a cell is formatted as a number, R sees raw integers with no hint that they represent dates. This function takes those integers and returns a vector of class Date. Fractional values become time-of-day when you opt in.

It accepts NA, vectorises cleanly, and handles the modern Windows convention and the older Mac 1904 convention through a single argument switch. Return type is Date by default, or POSIXct when include_time = TRUE.

Syntax and arguments

The signature has five arguments; you usually pass only the first. The remaining four cover legacy Mac files, time-of-day handling, sub-second precision, and timezone.

date_system is the only argument you change for files saved by older Mac versions of Excel. Microsoft used a different origin on Mac through Excel 2011, shifting every serial by 1462 days. Pass "mac pre-2011" and the function applies the correct origin.

Examples by use case

Four patterns cover most real-world calls to excel_numeric_to_date(). Each example below is self-contained and runs in a fresh R session.

Use 1: a single serial number. Useful for sanity-checking what a number represents before bulk converting.

The function returns a Date object you can compare, subtract, or format with format() like any other R date.

Use 2: a numeric column inside a data frame. This is the canonical Excel-import fix. A CSV exported from Excel ships date columns as numbers; pipe the column through the function in a mutate().

raw_date was a numeric column with values nobody could read at a glance. sale_date is now a real Date that downstream filters and joins can use.

Use 3: include the time component. Excel stores time as a fraction of a day (0.5 = noon, 0.75 = 6 PM). Pass include_time = TRUE to recover a POSIXct timestamp.

The fractional part rolled through midnight, 6 AM, noon, and 6 PM. Use this whenever an Excel cell carries both a date and a time stamp.

Use 4: legacy Mac files. Excel 2011 and earlier on Mac used 1904-01-01 as origin. Files saved in that era will be off by 1462 days when read with default settings.

The same serial means different dates depending on which system saved the file. Check the source application before bulk converting.

Compare with alternatives

Four other tools convert dates in R, but none target Excel serials specifically. Pick excel_numeric_to_date() for numeric input from Excel; pick the others for character input or non-Excel numeric formats.

For interactive cleanup of Excel data, excel_numeric_to_date() is the shortest option. The base R formula as.Date(x, origin = "1899-12-30") works for the modern system but offers no Mac switch. Reach for lubridate when input is already a character date string.

Common pitfalls

Three traps recur, each easy to spot once you know what to look for. All three are properties of the Excel-to-R conversion path, not bugs in the function.

The function refuses character input by design. Coerce with as.numeric() first, or use readr::parse_number() if the strings carry currency symbols or commas.

The same number means different dates depending on origin. If the conversion result looks off by roughly four years, suspect the wrong date_system and try the Mac flag.

Numbers like 1, 2, 5 produce dates in 1899, which is almost never what you want. A column with single-digit values is probably not a date column; double-check the column meaning before converting.

Try it yourself

Related janitor functions

janitor groups excel_numeric_to_date() with five other import-cleanup helpers. Together they handle the most common spreadsheet quirks.

• convert_to_date() accepts mixed numeric and character input and dispatches to the right converter
• convert_to_datetime() does the same but always returns POSIXct
• excel_time_to_numeric() reverses the operation for diagnostic checks
• sas_numeric_to_date() is the SAS-origin analogue for serials with a 1960-01-01 origin
• clean_names() standardises column names after the import

For a tour of the package's full toolkit, see the janitor Package in R tutorial. The official function reference lives at sfirke.github.io/janitor.

FAQ

Why does excel_numeric_to_date(1) return 1899-12-31 instead of 1900-01-01?

Excel incorrectly treats 1900 as a leap year, inheriting the bug from Lotus 1-2-3 for backward compatibility. To match Excel's calendar arithmetic exactly, janitor uses 1899-12-30 as the modern origin. Serial 1 therefore lands on 1899-12-31, serial 2 on 1900-01-01, and so on. The result agrees with what Excel itself displays.

Can I pass a character vector that contains numeric date serials?

No. The function checks the class of date_num and errors if it is not numeric or integer. Coerce upstream with as.numeric(x) if the column came in as character, or use readr::parse_number(x) when the strings contain stray characters like dollar signs or commas. The strict check prevents silent misinterpretation of non-numeric input.

How do I convert R dates back to Excel serial numbers?

Subtract the modern origin and coerce to numeric: as.numeric(date - as.Date("1899-12-30")). For a vector of dates, the same formula works element-wise. For the Mac legacy system, use as.numeric(date - as.Date("1904-01-01")). Wrapping the calculation in a small helper makes round-trips through Excel cleaner.

Does the function handle timezones?

Only when include_time = TRUE. The tz argument is forwarded to the POSIXct constructor and controls how the resulting timestamp is displayed, not how the serial is interpreted. Excel itself stores no timezone in a serial, so the choice is yours; UTC is a safe default for storage, and a local timezone is appropriate when the file represents local-time events.

What happens with negative serial numbers?

Negative serials map to dates before 1899-12-30 and the function returns them without complaint. Excel itself handles pre-1900 dates poorly, so negative serials usually signal a data error or a column that is not really a date. Inspect the source before trusting the result.