2

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?

Greggory Wiley
  • 660
  • 6
  • 16
  • why are you tagging the question as node? how is this related to node? – Lelio Faieta May 27 '22 at 16:38
  • 1
    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 <- looks like its an excel serial date this question solved my problem but did not help me identify what type of date it was, the answers above made me realize it might be some type of spreadsheet specific date. – Greggory Wiley May 27 '22 at 16:54

1 Answers1

0

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"))
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20