Using dates in Google Sheets

Question

Dates are giving a bit of trouble in Google Sheets. I'm basically trying to subtract dates but it's not working. However first, I'm trying to understand how Dates work. Here's an example of an odd behavior with dates.

function addDates(sheet)
{
var prevDateCurrYear = new Date();
Logger.log(prevDateCurrYear);
Logger.log(prevDateCurrYear.getMonth()+' '+prevDateCurrYear.getDay()+' '+prevDateCurrYear.getYear());
}

This is the Log

[15-02-03 18:15:21:631 EST] Tue Feb 03 18:15:21 GMT-05:00 2015
[15-02-03 18:15:21:632 EST] 1 2 2015

The getMonth and getDay doesn't seem to work. It should be pulling 2 and 3 but instead pulls 1 and 2. Why is this happening?

I'm using this documentation: https://developers.google.com/apps-script/reference/contacts/date-field

Answer 1

Actually getMonth and getDay are Java script methods. The way the getMonth and getDay works different in Javascript. You can refer to these pages for more information.

http://www.w3schools.com/jsref/jsref_getday.asp

http://www.w3schools.com/jsref/jsref_getmonth.asp

Hope that helps!

Answer 2

Couple things to consider. 1. Set the Time Zone in your script Project Properties. File, Project Properties. 2. Using Utilities.formatDate() can relieve a lot of headaches as you can standardize your dates to your needs. 3. getDay() and getDate() return "day of the week (i.e. 1-7)" and "the date of the month (i.e. 1-31)", respectively.

here is some more details about dates to clarify:

function addDates() {
  var prevDateCurrYear = new Date();
  var year = Utilities.formatDate(prevDateCurrYear, "America/Denver", "yyyy");
  var date = Utilities.formatDate(prevDateCurrYear, "America/Denver", "d");
  var month = Utilities.formatDate(prevDateCurrYear, "America/Denver", "M");
    Logger.log("\nUnformatted prevDateCurrYear: "+prevDateCurrYear+
      "\n\nYour original log:\nprevDateCurrYear.getMonth(): "+prevDateCurrYear.getMonth()+"\n"+
      "prevDateCurrYear.getDay(): "+prevDateCurrYear.getDay()+"\n"+
      "prevDateCurrYear.getYear(): "+prevDateCurrYear.getYear()+"\n"+
      "\nUsing Utilites.formatDate():\nmonth: "+month+"\ndate: "+date+"\nyear: "+year+
      "\nUtilities.formatDate(prevDateCurrYear, \"America/Denver\", \"M d yyyy\"): "
      +Utilities.formatDate(prevDateCurrYear, "America/Denver", "M d yyyy"));
}

Log:

Unformatted prevDateCurrYear: Thu Feb 18 2016 11:29:42 GMT-0700 (MST)

Your original log:

prevDateCurrYear.getMonth(): 1

prevDateCurrYear.getDay(): 4

prevDateCurrYear.getYear(): 2016

Using 'Utilites.formatDate()`:

month: 2

date: 18

year: 2016

Utilities.formatDate(prevDateCurrYear, "America/Denver", "M d yyyy"): 2 18 2016