4

I'm trying to create a Google App Script that adds one month to a date already on a Google Spreadsheet. In the example below I was trying to take the date from cell B2 (1/1/16) and add one month and then place the new date in cell B4 (2/1/16). Using the script below I get the result of 1454313600000 instead of 2/1/16. I haven't been able to figure out what I need to do to get a result of 2/1/16.

function myFunction(DateTest) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var value = sheet.getRange('B2').getValue();
  var a1date = value.getMonth();
  var newdate = value.setMonth(a1date+1);

  sheet.getRange('B4').setValue(newdate);
}
8
  • Dates are stored as numbers. I'll wager that if you format the cell as a date it'll be the date you expect. – ale Jan 10 '16 at 23:32
  • What do you mean "add one month"? If the date is January 31st, what should the result be? – user79865 Jan 10 '16 at 23:38
  • @AlE. When I format the cell as date it is still displaying 1454313600000 – Michael S. Jan 11 '16 at 2:48
  • @Normal The date will always be on the first of the month, if the date was January 1st I would like the result to be February 1st. – Michael S. Jan 11 '16 at 2:50
  • Okay. Do you have to use Apps Script for this? The added conversion layers are more of a nuisance with dates; the built-in formulas could do this task just fine. – user79865 Jan 11 '16 at 2:51
5

The issue here is on JavaScript level, not specific to Apps. The method setMonth that you use modifies the date object on which it's called. You don't need any assignment on that line. MDN docs don't even say what setMonth is supposed to return, but apparently it returns the millisecond timestamp corresponding to the new date. In any case, this is what works:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  var value = sheet.getRange('B2').getValue();
  var a1date = value.getMonth();
  value.setMonth((a1date+1) % 12);   // no assignment needed; also, mod 12
  sheet.getRange('B4').setValue(value); 
}

Notice that % 12 is needed to get from December to January.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.