How to increase month by 1 in Google Sheet ?

👤 Diwas Poudel 🕒 27 May 2023 📁 FIX

Suppose you have a series of dates in your google sheets and you would like to increase the month part by 1 without changing the year part and day part. If you have large series of dates then manually adding 1 to a month is time consuming and irritating process. So there should be a process/formula. when you update the source cell then automatically reflects the destination cell. Here, I will explain the various processes for increasing month by 1 in Google Sheets.

Method 1: Using the DATE function

We can use the inbuilt DATE() function for increasing month by 1 and its default syntax look like this:

=DATE(year,month,day)

By adding 1 to the monthly part we can easily obtain the required result.

=DATE(YEAR(CellNumber),MONTH(CellNumber)+1,DAY(CellNumber))

Example:

Method 2: Using the EDATE function

It is one of the easiest ways for increasing the month by 1 from given series of dates.

The syntax looks like this:

EDATE(start_date, [months])

The arguments of EDATE() function are-

start_date – Start date as a valid Google sheet date.

months – Number of months before or after start_date.

Example:

=EDATE(A2,1)

Here, A2 is a cell containing the actual date before incrementing, 1 is for incrementing 1 month.

edate-function-googlesheets

Method 3: Using If Statement

If Statement can also be used for increasing month by 1.

Use Case:

=IF(DAY(EDATE(A2,1))<DAY(A2),NA(),EDATE(A2,1))

Method 4: Using Google App Scripts

We can use the Google Apps script for generating the date whose month is greater by one than the previous one. Here, we are using A2 as source data and B2 as destination data.

Scripts look like this:

function IncreaseMonthByOne() {
  var spreadsheet = SpreadsheetApp.getActive();     // Getting Active spreadsheet
  var sheet = spreadsheet.getSheetByName('Sheet1'); // Sheet1 is sheet name
  var value = sheet.getRange('A2').getValue();
  var a1date = value.getMonth();
  value.setMonth((a1date+1) % 12);   // %12 for calculating Month Value
  sheet.getRange('B2').setValue(value); 
}