addMonthsClamped(date, n) adds or subtracts whole months and clamps to the last day of the target month instead of rolling into the next one. January 31 plus one month is February 28 (or February 29 in a leap year), not March 2 or 3.
When to use
- Stepping a renewal, end-of-term, or invoice date by months
- Any "same day next month" rule that should stay at month-end when the day does not exist
- Avoiding the JavaScript
setMonthrollover that turns Jan 31 into a March date
How to use this snippet
/**
* Add or subtract whole months, clamping to the last day of the target
* month instead of rolling over (Jan 31 + 1 -> Feb 28 or Feb 29).
* The calendar day is taken in the spreadsheet timezone (script timezone
* if no spreadsheet is bound). The result is noon UTC on that day so a
* later formatDate in the same timezone stays on it. n must be an integer.
*
* @param {Date} date
* @param {number} n
* @return {Date}
*/
function addMonthsClamped(date, n) {
if (!(date instanceof Date) || isNaN(date.getTime())) {
throw new Error('addMonthsClamped: invalid date');
}
n = Number(n);
if (!isFinite(n) || Math.floor(n) !== n) {
throw new Error('addMonthsClamped: n must be an integer');
}
var tz;
try {
tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
} catch (e) {
tz = Session.getScriptTimeZone();
}
var parts = Utilities.formatDate(date, tz, 'yyyy-MM-dd').split('-');
var y = Number(parts[0]);
var m = Number(parts[1]) - 1;
var d = Number(parts[2]);
var target = m + n;
var y2 = y + Math.floor(target / 12);
var m2 = ((target % 12) + 12) % 12;
var last = new Date(Date.UTC(y2, m2 + 1, 0)).getUTCDate();
var day = Math.min(d, last);
// Noon UTC so formatDate in the spreadsheet timezone stays on this calendar day.
return new Date(Date.UTC(y2, m2, day, 12, 0, 0));
}
Example
function nextRenewal(start) {
return addMonthsClamped(start, 1);
}
// Jan 31 2027 -> Feb 28 2027
// Jan 31 2028 -> Feb 29 2028
// Feb 28 2027 -> Mar 28 2027 (a short month is not extended forward)
Notes
- The calendar day is read with
Utilities.formatDatein the spreadsheet timezone, the same pattern as Add Business Days. If nothing is bound, it falls back toSession.getScriptTimeZone(). - February 28 plus one month is March 28, not March 31. Clamping only shortens a day that does not exist in the target month.
- Invalid dates throw.
nmust be a whole number, positive or negative.0returns the same calendar day (at noon UTC). - Write the result back with
setValueas a Date, not as a pre-formatted string. For a display stamp, see Format In Spreadsheet Timezone.
Happy Coding!
