Adding a Month to a Date in a Google Spreadsheet with a Script

Question

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

Answer 1

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.

Answer 2

The formula in the accepted answer increments the month portion of a date by 1, however it will return incorrect and even invalid dates depending on the initial date entered in B2.

The correct formula must take into account the differing lengths of months, leap years, as well as whether the month is the last in the year.

For example:

2023-12-01 + 1 month ≠ 2023-01-01
2023-01-29 + 1 month ≠ 2023-02-29

The correct formula below was posted by @Jazaret in an answer at Stack Overflow

It does essentially the same job as the EDATE() spreadsheet function.

Working example where the result variable is written to the log:

function workingAddMonth() {
var myDate = new Date("2024-03-31");
var result = myDate.addMonths(1);

 console.log(result)
}

Date.isLeapYear = function (year) { 
    return (((year % 4 === 0) && (year % 100 !== 0)) || (year % 400 === 0)); 
};

Date.getDaysInMonth = function (year, month) {
    return [31, (Date.isLeapYear(year) ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month];
};

Date.prototype.isLeapYear = function () { 
    return Date.isLeapYear(this.getFullYear()); 
};

Date.prototype.getDaysInMonth = function () { 
    return Date.getDaysInMonth(this.getFullYear(), this.getMonth());
};

Date.prototype.addMonths = function (value) {
    var n = this.getDate();
    this.setDate(1);
    this.setMonth(this.getMonth() + value);
    this.setDate(Math.min(n, this.getDaysInMonth()));
    return this;
};