Convert Datetime to Excel datetime

Source: R/e_convert_ExcelDate_to_Date_ALL.R

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 SystemSerial number
    190040729
    190439267
e_convert_Datetime_to_ExcelDatetime(
  datetimes,
  origin = "1899-12-30 00:00:00 UTC"
)

Arguments

datetimes

list of Datetimes

origin

an origin datetime should correspond to your operating system and version of Excel

Value

datetimes list of numbers

Examples

datetimes = c("2011-03-04 06:00:00 UTC", "2011-03-11 07:54:59 UTC", "2011-03-13 07:54:59 UTC")
e_convert_Datetime_to_ExcelDatetime(datetimes)
#> [1] 40606.25 40613.33 40615.33