ExcelDateConversion.R

## Convert Excel Dates

# Differences between Windows and Mac Serial number date systems
#   https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487#ID0EAACAAA=Windows
#     Excel supports two date systems, the 1900 date system and the 1904 date system.
#     Each date system uses a unique starting date from which all other workbook dates are calculated.
#     All versions of Excel for Windows calculate dates based on the 1900 date system.
#     Excel 2008 for Mac and earlier Excel for Mac versions calculate dates based on the 1904 date system.
#     Excel 2016 for Mac and Excel for Mac 2011 use the 1900 date system, which guarantees date compatibility with Excel for Windows.
#   For example, July 5, 2011, can have two different serial numbers, as follows:
#     Date System   Serial number
#     1900          40729
#     1904          39267

# Excel date conversion
f_ExcelDate_to_Date <-
  function(
    dates
  ) {
 ## dates <- 42917:42919
 ## f_ExcelDate_to_Date(dates)

  as.Date(
    dates
  , origin = "1899-12-30"       # using 1900 Date system
  , tz = "UTC"
  ) %>%
  return()
}

f_Date_to_ExcelDate <-
  function(
    dates
  ) {
 ## dates <- c("2017-07-01", "2017-07-02", "2017-07-03")
 ## f_Date_to_ExcelDate(dates)

  as.numeric(
    as.Date(dates) -
    as.Date(
      0
    , origin = "1899-12-30"     # using 1900 Date system
    , tz = "UTC"
    )
  ) %>%
  return()
}

# Excel datetime conversion
f_ExcelDatetime_to_Datetime <-
  function(
    datetimes
  ) {
  ## datetimes = c(40606.25, 40613.32986, 40615.32986)
  ## f_ExcelDatetime_to_Datetime(datetimes)

  as.POSIXct(
    datetimes * (60 * 60 * 24)
  , origin = "1899-12-30"       # using 1900 Date system
  , tz = "UTC"
  ) %>%
  return()
}

f_Datetime_to_ExcelDatetime <-
  function(
    datetimes
  ) {
  ## datetimes = c("2011-03-04 06:00:00 UTC", "2011-03-11 07:54:59 UTC", "2011-03-13 07:54:59 UTC")
  ## f_Datetime_to_ExcelDatetime(datetimes)

  as.numeric(
    as.POSIXct(datetimes, tz = "UTC") -
    as.POSIXct(
      0
    , origin = "1899-12-30 00:00:00 UTC"     # using 1900 Date system
    , tz = "UTC"
    )
  ) %>%
  return()
}