janitor (version 1.1.1)

excel_numeric_to_date: Convert dates encoded as serial numbers to Date class.

Description

Converts numbers like 42370 into date values like 2016-01-01.

Defaults to the modern Excel date encoding system. However, Excel for Mac 2008 and earlier Mac versions of Excel used a different date system. To determine what platform to specify: if the date 2016-01-01 is represented by the number 42370 in your spreadsheet, it's the modern system. If it's 40908, it's the old Mac system. More on date encoding systems at http://support.office.com/en-us/article/Date-calculations-in-Excel-e7fe7167-48a9-4b96-bb53-5612a800b487.

Usage

excel_numeric_to_date(date_num, date_system = "modern",
  include_time = FALSE, round_seconds = TRUE)

Arguments

date_num

numeric vector of serial numbers to convert.

date_system

the date system, either "modern" or "mac pre-2011".

include_time

Include the time (hours, minutes, seconds) in the output? (See details)

round_seconds

Round the seconds to an integer (only has an effect when include_time is TRUE)?

Value

Returns a vector of class Date if include_time is FALSE. Returns a vector of class POSIXlt if include_time is TRUE.

Details

When using include_time=TRUE the returned object will not include a time zone since excel does not store dates and times with time zones. When adding time zones, ensure that any conversion done does not change the intended time including if the time is or is not in daylight savings time. Also, days with leap seconds will not be accurately handled as they do not appear to be accurately handled by Windows (as described in https://support.microsoft.com/en-us/help/2722715/support-for-the-leap-second).

Examples

# NOT RUN {
excel_numeric_to_date(40000)
excel_numeric_to_date(40000.5) # No time is included
excel_numeric_to_date(40000.5, include_time = TRUE) # Time is included
excel_numeric_to_date(40000.521, include_time = TRUE) # Time is included
excel_numeric_to_date(40000.521, include_time = TRUE,
  round_seconds = FALSE) # Time with fractional seconds is included
# }