I have a start and end date. How can I increment months between these two dates? It’s possible in Google Sheets. The formula is also not complicated. In Google Sheets, you can increment months between two given dates.
I’ve been quite amazed by the different formula requirements of spreadsheet users. This formula is one among them.
In this Google Spreadsheet example, I have a start date in Cell A2 and end date in Cell A3. What I want is, add exactly one month to the start date and increment up to the end date.
As you can see, the start date is 5/10/2017. Then obviously the next incremented date will be 5/11/2017. You can see the result in Column C above. Below you can find the formula used in Cell C2.
The Formula to Increment Months Between Two Given Dates in Google Sheets
=ArrayFormula(edate(A2,row(A1:indirect("A"&datedif(A2,A3,"M")))))
I think you should know how this formula increment months between two given dates in Google Sheets.
Formula Explanation
To learn this formula, you may want to understand the use of the functions involved. The functions are EDATE, DATEDIF, ROW, INDIRECT and ARRAYFORMULA.
I’m trying to explain you the logic of the above formula that increments months in Google Sheets.
Do you know which is the core function in the above formula? It’s the EDATE function. Because the EDATE function can increment months based on arguments. Other functions are just feeding the arguments to EDATE.
Syntax:
EDATE(start_date, [months])
Just feed this function with a start date and months. For example, in the above example, the start date is 5/10/2017.
So the below formula which will return the date 5/11/2017.
=edate(A2,1)
or
=edate("5/10/2017",1)
To return 5/12/2017, change the second argument, i.e., 1 in the formula to 2. This’s the logic I’ve used in the above formula.
There I’ve automatically fed the formula with numbers 1,2,3 ….8 with the help of ROW function.
There I’ve auto fed months from 1 to the total number of months between the start date and end date. No clue? I’ll detail it.
The total number of months between 5/10/2017 and 11/6/2018 is 8. In my formula that increment months between two given dates, I’ve used the below formula to find the total number of months.
=datedif(A2,A3,"M")
The row function can auto feed 1 to 8 as arguments to the EDATE.
See the main formula again.
=ArrayFormula(edate(A2,row(A1:indirect("A"&datedif(A2,A3,"M")))))
Here A2 is the “start_date” argument of the EDATE function. Then the rest of the formula returns the number 1 to 8 and which is the “months” arguments.
The actual row formula is;
=ArrayFormula(row(A1:A8))
The INDIRECT and DATEDIFF function return “A8”.
That’s all. You can follow the above tutorial to increment months between two given dates in Google Sheets.
If you want to increment days, then there is another tutorial. Find it below.
Similar:Â How to Auto Populate Dates Between Two Given Dates in Google Sheets
