Google Sheets – Increment Dates by One Month

google sheetsgoogle-sheets-dates

I'm using Google Sheets and want to set a date in say E:7, then in Column B I want the first row to be the value E:7 and then all the rows below that to be incremented by exactly one month from the one above. I know I can set B:1 to =E:7 however I can't figure out the best way to implement after that. Simply saying B:1 + 30 doesn't work because not all months have 30 days.

Is there a way to do this?

Best Answer

The formula you can use to increment dates by exactly one month from a given date is:

=ArrayFormula(EDATE(E7,SEQUENCE(14,1,1,1)))

Functions used:

Related Solutions

Google Sheets Script – Add a Month to a Date

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.

Google-sheets – Pull a value for the following month in Google Sheets

Nevermind, I found an answer online after much searching. Here's what the formula looks like on my sheet, if anyone else is interested:

=VLOOKUP(INDEX($A$5:$A$41,MATCH(TRUE,$A$5:$A$41>=TODAY(),0)), A5:B41, 2, FALSE)

There's probably a way to optimize it somehow, but it's working so I'm happy.

Best Answer

Related Solutions

Google Sheets Script – Add a Month to a Date

Google-sheets – Pull a value for the following month in Google Sheets

Related Topic