0

I'm working with unformatted values received via Google Sheets REST API. One of the columns is a timestamp which only reads as a single number. Looks neither like a Unix timestamp nor JS timestamp. For example, 2020-12-09T20:55:14+08:00 comes as 44174.87168438657.

What is this format and how do I convert it to Unix timestamp or JS Date?

starleaf1
  • 2,701
  • 6
  • 37
  • 66
  • It might be the same as excel which does something weird (https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252#:~:text=Excel%20stores%20dates%20as%20sequential,date%20values%20to%20serial%20numbers.) – Antony Dec 09 '20 at 14:27
  • I am not a google sheets expert but I think it might be related to this: https://support.google.com/docs/answer/3093357 (" *If value is a date or time, N returns a generated serial number, based on the number of days since December 30, 1899.* ") – secan Dec 09 '20 at 14:41

1 Answers1

1

SERIAL_NUMBER outputted by the API is the number of days since December 30, 1899 and the fractional portion (right of the decimal) counts the time as a fraction of the day.

You could verify the start date by using =TO_DATE in your spreadsheet.

Example:

enter image description here

To convert the serial date into a Javascript date, you could follow silkfire's comment here.

References

DateTimeRenderOption

TO_DATE()

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • 1
    If this is like the MS equivalent, you need to be careful with negative numbers. The integer part is -ve days, but the decimal part is +ve time, so -2.25 is 06:00 on 28 Dec 1899 not 18:00 on 27 Dec 1899 (i.e. subtract 2 days then add 0.25 days). – RobG Dec 10 '20 at 05:49