Converting Google spreadsheet date into a JS Date object?

I've been going round in circles on this one... I've got a spreadsheet which holds two dates, and I need to find the number of elapsed years between the two (ie. someone's age at a given date; this is a replacement for Excel's DATEDIF).

The first step is to convert Google's serial number into a JS Date object, but there doesn't appear to be Date constructor that does this. Any ideas?

Thanks.

Answers:

Answer

I know you are happy with your solution as it stands, but I just wanted to add my observations of how Google Apps Script deals with "dates", either passed in a custom function, or retrieved from a cell with getValue().

My rule of thumb is that if Sheets (the spreadsheet application) is providing a value formatted as a date (either by automatic coercion, or the user setting the format), then Google Apps Script will automatically hold this value as a date object.

Eg:

function returnDate(value) {
  return new Date(value);  
}

If you enter 1/1/13 in A1, and in another cell you invoke =returnDate(A1), it will return the same date (as it would if you simply had return value; in the code). However, watch what happens when you format A1 as "Normal" (convert it to a numerical value). Here, the "Sheets serial number" (number of days from 30/12/1899) is converted into a date object by Google Apps Script, but in GAS it is "regarded" as the number of milliseconds from midnight 1/1/1970. So you might get unexpected results if you are passing numerical values that you believe are representative of a date.

Also compare:

=returnDate(DATE(2013;1;1))

=returnDate(VALUE("1/1/13"))

=returnDate(DATEVALUE("1/1/13"))

=returnDate("1/1/13")

=returnDate("1/1/2013")

The latter two "work", because new Date() successfully creates the date object from a valid string, but note that Sheets automatically coerces to the current century, while GAS coerces a two-digit year to the 1900's.

So IMO if you wanted it to behave exactly as it would in Excel (that is, "regard" a numerical value as a serial number for a date), you would need to first test if the passed parameter is a date object (or "valid" text string), and if not, mathematically convert it from "days from 30/12/1899" to "milliseconds from 1/1/1970", and then new Date() it.

Apologies for the long-winded post.

Answer

To convert a Google spreadsheet date to a javascript date :

var JSdate = Date.parse(Cell.getValue())

To convert a javascript date to a Google spreadsheet date:

function GoogleDate( JSdate ) { 
   var D = new Date(JSdate) ;
   var Null = new Date(Date.UTC(1899,11,30,0,0,0,0)) ; // the starting value for Google
   return ((D.getTime()  - Null.getTime())/60000 - D.getTimezoneOffset()) / 1440 ;
}
Answer

This is what I did:

function numberToDate(number){
  var date = new Date(number * 24 * 60 * 60 * 1000);
  date.setFullYear(date.getFullYear() - 70);
  date.setDate(date.getDate() - 1);
  return (date);
}

This may seem a bit dirty, but this is the only solution I found for now

Answer

After some more experimenting, it turned out that it just works, which was a bit of a surprise. new Date(cell) seems to internally convert the serial number into a string which is sufficient to create the date object. Full answer:

function datedif(first, second, format) {
  var e1  = new Date(first);
  var e2  = new Date(second);
  var age = e2.getFullYear() - e1.getFullYear();
  if(
      (e2.getMonth() <  e1.getMonth()) || 
     ((e2.getMonth() == e1.getMonth()) && (e2.getDate() < e1.getDate())))
    age--;
  return age;
}
Answer

You can try like this:

return new Date(1900, 0, --excelDate)
Answer

By "serial number" I'm guessing you're talking about a unix time in seconds or milliseconds from the epoch. You can simply use the standard Javascript Date object:

new Date(value);

Google is your friend from there. Here's some references to start you off:

Javascript allows you to do simple subtraction with two Dates, returning you the time difference in ms.

var timeDiffInMS = date2 - date1;

That should be all you need to figure it out, so I'll leave the years calculation as an exercise for the reader.

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.