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, 2016 at 23:32
  • What do you mean "add one month"? If the date is January 31st, what should the result be?
    – user79865
    Jan 10, 2016 at 23:38
  • @AlE. When I format the cell as date it is still displaying 1454313600000
    – Michael S.
    Jan 11, 2016 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, 2016 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, 2016 at 2:51

2 Answers 2

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
1

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;
};
1
  • +10 Great example of when it makes sense to answer a very old (2016) question in spite of an an accepted answer. I suggested some edits to improve the answer. Keep contributing!
    – Blindspots
    Mar 12, 2023 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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