Skip to content

Latest commit

 

History

History
52 lines (40 loc) · 2.32 KB

README.md

File metadata and controls

52 lines (40 loc) · 2.32 KB
Raw

Date

This script is developed by Jeroen Geradts is licensed under the Creative Commons Attribution 4.0 International License. It contains a library with functions that convert date and time among several formats.

Input. This script accepts these inputs:

  • Dutch date and time string (DD-MM-YYYY hh:mm:ss)
  • Excel date (serial number between 1 and 2958101.99999)
  • ISO 8601 short (YYYYMMDD)
  • JS primitive value of a date object

Functions. You can sent the input to one of these functions:

  • $date(input)
  • $dateFromDutchDateAndTimeString(input)
  • $dateFromExcelSerialNumber(excel)
  • $dateFromISO8601Short(input)
  • $dateFromJS(input)

$date() is a general function that will analyse the input and invoke the most likely of the other functions to do the conversion. If you only have JS primitive values as input, use $dateFromJS() instead.

Output. All functions deliver an array with these values:

  • year (YYYY)
  • JS primitive value of date and time (milisenconds)
  • JS primitive value of 1st of the month (miliseconds)
  • ISO 8601 short value for date (YYYYMMDD)
  • excel serial number

Examples:

  • $date("02-02-2000 12:00:00") = [2000,949489200000,949359600000,20000202,36558.5]
  • $date(949489200000) = [2000,949489200000,949359600000,20000202,36558.5]
  • $date(949359600000) = [2000,949359600000,949359600000,20000201,36557]
  • $date(20000202) = [2000,949446000000,949359600000,20000202,36558]

Attention:

  • 1900-02-29: Non existing date in Excel (Excel serial number 60)
  • 1916-04-30: JS-values are 600 sec off after 23:40 (around Excel number 5965)
  • 1916-05-01: JS-values are 600 sec off before 01:00 (around Excel number 5966)
  • 1937-06-30: JS-values are 28 sec off after 22:50 (around Excel number 13696)
  • 1937-07-01: JS-values are 28 sec off before 0:00 (around Excel number 13697)
  • 1940-05-15: JS-values are 6000 sec off after 23:40 (around Excel number 14746)
  • 1940-05-16: JS-values are 6000 sec before 01:40 (around Excel number 14747)
  • 1970-01-01: JS-values between 01:00:00 and 01:49:18 are treated as Excel serial numbers in $date()
  • 1970-01-01: JS-values after 03:46:00 are treated as ISO 8601 short numbers in $date()

These exceptions are the result of differences between the Excel, ISO 8601 and JS-date systems. And because Excel does not account for time corrections in 1916, 1937 and 1940. The script corrects Excel serial numbers before 61.