-1

I have a cell in my google spreadsheet with the value of 5/31/2016 0:22:38, but when I wrote a script to fetch the data it returns 42521.015713761575. I've tried some codes from jquery convert number to date? but most returns dates back to 1970. How would I be able to format the said float into the date specified above? thanks... :)

Community
  • 1
  • 1
Manson Mamaril
  • 153
  • 2
  • 13
  • 2
    Can you please update your question with your script? – David R Aug 07 '16 at 15:56
  • Possible duplicate of [How to read the correct time values from Google Spreadsheet](http://stackoverflow.com/questions/17715841/how-to-read-the-correct-time-values-from-google-spreadsheet) – Rubén Aug 07 '16 at 22:14

1 Answers1

5

This is apparently a number of days elapsed since December 30, 1899, 00:00:00.

You can convert it back to a UTC date with the following code (note that months are zero-based, so December = 11):

var x = 42521.015713761575;

// seconds in day = 24 * 60 * 60 = 86400
var date = new Date(Date.UTC(1899, 11, 30, 0, 0, x * 86400));

console.log(date.toUTCString());
Arnauld
  • 5,847
  • 2
  • 15
  • 32
  • Wed, 01 Jun 2016 16:22:37 GMT is the result on JSFiddle... getting closer? maybe a timezone problem? :) – Manson Mamaril Aug 07 '16 at 17:06
  • @MansonMamaril I've updated my code to create a UTC date, so that at least everyone is seeing the same thing. :-) I've also updated the reference day (2 days before Jan 1st, 1900) so that it matches the expected result. But this still looks very hackish and unreliable. I would recommend to look for a dedicated method. (Are you sure Google is not providing one?) – Arnauld Aug 07 '16 at 17:16
  • I've tried a utilities.formatdate with tutorials from different stackoverflow but they're also providing dates from 1970... – Manson Mamaril Aug 08 '16 at 17:32
  • I don't know if there's an actual function to return what is written... I use this function `sheet.getRange(startingrow, startingcol, lastrow, lastcol).getValues();` to get data in batch but only float is returned when the fetching lands on the date value... – Manson Mamaril Aug 08 '16 at 17:33
  • [This post](http://webapps.stackexchange.com/questions/87186/why-does-google-spreadsheet-use-days-as-unit-of-time-when-entering-a-number) suggests that the reference time in Google spreadsheets is indeed December 30, 1899 00:00:00. So it seems like my hackish answer may actually be correct. – Arnauld Aug 08 '16 at 18:54