How to identify unknown date time format which is five digits followed by twelve digits, and deal with in NodeJs

Question

Checked several other Stack Overflow Questions, searched for a while through all unknown date format questions, none I found look like this. I need to deal with this type of date time format in Javascript, which Google Sheets and LibreOffice Calc both are able to recognize and parse as 5/24/2023 10:00 for this example.

45070.416666666664 <- what date time format is this?

why are you tagging the question as node? how is this related to node? — Lelio Faieta, May 27 '22 at 16:38
It looks like it's the number of days since 1900-01-01. 45070 divided by 365.25 is 123.4. — Barmar, May 27 '22 at 16:39
How to identify unknown date format and then deal with it in NodeJs. — Greggory Wiley, May 27 '22 at 16:39
have a look at [this](https://www.ablebits.com/office-addins-blog/google-sheets-change-date-format/) to see how the date is built — Lelio Faieta, May 27 '22 at 16:39
@Barmar it is the integer of the days passed from dec 30 1899. So 1 is dec 31 1899 and so on. The decimals are for the time — Lelio Faieta, May 27 '22 at 16:41
@GreggoryWiley _I need to deal with this type of date time format in Javascript_ so is it either node or javascript?? — Lelio Faieta, May 27 '22 at 16:42
@LelioFaieta Isn't that essentially the same thing? The time is a fraction of a day, e.g. `.5` is noon. — Barmar, May 27 '22 at 16:42
https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript — Greggory Wiley, May 27 '22 at 16:54

score 0 · Answer 1 · answered May 28 '22 at 01:13

In GSheets (as others), change format to date.

In app script, use

function myFunction() {
  const sh = SpreadsheetApp.getActiveSheet()
  const value = sh.getRange('C1').getValue()
  console.log (Utilities.formatDate(value, Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm"))
}

How to identify unknown date time format which is five digits followed by twelve digits, and deal with in NodeJs

1 Answers1