Converting Google spreadsheet date into a JS Date object?

google script string to date
google script date add days
google apps script compare dates
javascript date
javascript date format
javascript date library
google sheets convert date to month
date time google sheets script

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.

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.

Convert long date/time variable into just date in Google Script, Dates from the spreadsheet will be imported in the script as 'date object'. so let's say, (again as an example) you get the date from A1. var date = ss. This guide describes important concepts, common pitfalls, and recommended approaches when working with dates and times in Google Ads scripts. Basic concepts. To work with dates and times in Google Ads scripts, use JavaScript's built-in date object. A JavaScript date object represents a particular moment in time.

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 ;
}

Working with Dates and Times | Google Ads scripts, A JavaScript date object represents a particular moment in time. In that mistaken view, date would need to be "converted" to be used in other timezones. Google Ads scripts often write output to a spreadsheet, including  A date field in a Contact. This class is only used by the Contacts service, and dates used elsewhere in App Script use JavaScript's standard Date object. Gets the day of the month for this date. Gets the label for this field. Gets the month for this date. Gets the year for this date. Sets the date to this day, without a year.

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

Basic date manipulation in Google script, Conversely, you can assign a Date object to a cell using setValue , and The timestamp in Google Sheets is in local timezone, which is set via File a date object is assigned to a variable, then the getMonth() JavaScript So, if you want to use JavaScript date functions, first should convert the spreadsheet  Conversion from object to string in Google Apps Script act on the JavaScript Date object, minutes in Google Apps Script while reading Google Spreadsheet data. 3.

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;
}

Convert Excel date values to JavaScript date objects · GitHub, Convert Excel date values to JavaScript date objects - ExcelToJsDate.js. of days between Jan 1, 1900 and Jan 1, 1970, plus 1 (Google "excel leap year bug"​). Ensure that the input to the function is either a reference to a cell containing a date, a function which returns a date object such as DATE, DATEVALUE or TO_DATE, or a date serial number of the type returned by the N function. Google Sheets represents dates as numbers; while conversion is automatic when a human-readable date is entered into a

Date.parse(), It is not recommended to use Date.parse as until ES5, parsing of strings was (​See the section Date Time String Format in the ECMAScript specification for more different values depending on the format of the string that is being converted. Google Chrome will accept a numerical string as a valid dateString parameter. I'd like to expose some dates into Javascript so that it feels as native as possible (i.e. they are JS date objects, not java.util.Date). Rhino doesn't seem convert the Java date type into the native JS type. So only the java date methods are available to the user. I'm happy to manually instantiate a "native" Javascript object, but

Date Functions in Standard SQL | BigQuery, Extracts the DATE from a DATETIME expression. Return Data Type. DATE. Example. SELECT DATE(2016,  Our script is expecting a JSON data-type so we’ll have to convert our form data into a JSON object. I chose to do this with the jQuery Serialize Object script, which provides the serializeObject

JavaScript Date Reference, The Date object is used to work with dates and times. Date objects are created with new Date() . There are four ways of instantiating a date: var d = new Date(); Apps Script allows you to connect Google Forms with Google Sheets through Forms and Spreadsheet services. This feature can automatically create a Google Form based on data in a spreadsheet. Apps Script also enables you to use triggers, such as onFormSubmit to perform a specific action after a user responds to the form.

Everything You Need to Know About Date in JavaScript, By default, almost every date method in JavaScript (except one) gives In JavaScript, if you want to use a date string, you need to use a format that's accepted worldwide. HH and mm at a minimum (or Google Chrome returns an invalid date). Next, let's talk about converting a date into a readable string. To get dates, change the cell format to Date. If providing an explicit string input to DATEVALUE rather than a cell reference, surrounding quotation marks are required. Some date formats are not understood by Google Sheets. To quickly ensure that the desired date format is understood, type an example of it into an empty cell, without quotation

Comments
  • Take a look at the formula's in this spreadsheet: docs.google.com/spreadsheet/…
  • No go, I'm afraid - you can't use spreadsheet functions in apps-script, so you have to write it from scratch.
  • That last post in this thread suggests that you can get the spreadsheet date, use some spreadsheet javascript utilities to convert it to a text format that the javascript date constructor can handle and then feed that string to the javascript date constructor. If you get serious about Googling this problem, I suspect you will find an answer.
  • A version of the function Adam describes is in this answer.
  • I know this is not suppose to be done, but you just saved my life! :)
  • Google seems to store date values in the same way as Excel, as days from the last day of 1899, so Google must be doing some magic somewhere. Still, 'new Date(value)' works, so I'll mark you up... :)
  • Serial Number is not the same as Epoch time.