Converting Google spreadsheet date into a JS Date object?

Question

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.

Answer 1

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 2

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.