Have you ever wondered how to increment months between two given dates in Google Sheets? Well, guess what? It’s completely possible! And the best part is, the formula is not complicated at all. So, let me show you how you can effortlessly increment months between two dates in Google Sheets.
In this Google Spreadsheet example, let’s say we have a start date in Cell A2 and an end date in Cell A3. Our goal is to add exactly one month to the start date and increment it up to the end date.
For instance, if the start date is 5/10/2017, the next incremented date will be 5/11/2017. You can see the result in Column C. Here’s the formula used in Cell C2:
=ArrayFormula(edate(A2,row(A1:indirect("A"&datedif(A2,A3,"M")))))I know this formula might look a bit overwhelming at first, but let me break it down for you.
The Formula to Increment Months Between Two Given Dates in Google Sheets
The core function in this formula is the EDATE function. It’s responsible for incrementing the months based on the arguments provided. The other functions—DATEDIF, ROW, INDIRECT, and ARRAYFORMULA—are just there to feed the necessary arguments to EDATE.
The syntax for the EDATE function is as follows:
EDATE(start_date, [months])You simply need to provide the start date and the number of months you want to increment. For example, in our case, the start date is 5/10/2017. So, if we use the formula =edate(A2,1), it will return the date 5/11/2017.
To increment to 5/12/2017, you can change the second argument in the formula to 2. This is the logic used in the formula mentioned earlier.
To automatically feed the formula with the numbers 1, 2, 3…8 (representing the months), we use the ROW function. The ROW function automatically increments the values from 1 to the total number of months between the start date and end date.
To find the total number of months between two dates, we use the DATEDIF function. For example, =datedif(A2,A3,"M") will return 8, which is the total number of months between 5/10/2017 and 11/6/2018.
Putting it all together, the formula becomes:
=ArrayFormula(edate(A2,row(A1:indirect("A"&datedif(A2,A3,"M")))))The A2 in the formula represents the “start_date” argument of the EDATE function, and the rest of the formula generates the numbers 1 to 8, which are the “months” arguments.
The actual formula used is:
=ArrayFormula(row(A1:A8))The INDIRECT and DATEDIF functions are there to determine the end range, which in this case is “A8”.
That’s all there is to it! With this tutorial, you can effortlessly increment months between two given dates in Google Sheets.
By the way, if you’re interested in incrementing days instead, we have another tutorial for that. You can find it here.
Similar: How to Auto Populate Dates Between Two Given Dates in Google Sheets
Now that you know this incredible trick, you can make the most out of your Google Sheets. Happy spreadsheet-ing!
