How to get a sequence of months or end of the months in Google Sheets?
For this, we can use the SEQUENCE function in combination with date functions.
If we want, we can replace the SEQUENCE function with the ROW function. You can find a similar tutorial here – Increment Months Between Two Given Dates in Google Sheets.
But I would suggest you stick with the former function as it’s more flexible.
Can you explain the advantages of using SEQUENCE over ROW here?
Yes! If you generate a sequence of months in Google Sheets using the SEQUENCE function, you can easily modify it for the following purposes.
- Change the orientation of the result from row to columns or vice versa without using TRANSPOSE.
- It helps us generate a sequence of months in descending (reverse chronological) order without using SORT.
The above two are the main advantages.
I have a total of four formulas included in this post, and they output values vertically (please see the four columns in the image below).
I will explain how to switch the orientation and more details under the relevant sections below.
Sequence of Months or End of the Months in Chronological Order in Google Sheets
Result in a Column (Vertically)
Please see the image above. I am going to talk about the formula in cell C3 that generates the dates in C3:C14.
=ArrayFormula(EDATE($B$1,SEQUENCE(12,1,0)))The above formula generates the sequence of months vertically in the range C3:C14.
To return the sequence of the end of the months, I have used another formula in cell D3.
=ArrayFormula(eomonth(EDATE($B$1,SEQUENCE(12,1,0)),0))Both formulas take the input, i.e., start date, in cell B1 and generate the result.
Formula Explanation
Let’s learn the first formula. The second formula is easy to catch then.
You may follow the below steps to quickly learn the above first Google Sheets formula.
Enter the following formula in any blank cell in your Google Sheets. Before that, don’t forget to insert the date 1-Jan-21 in cell B1.
=edate(B1,0)It will return the date 1-Jan-21.
Change the formula as below.
=edate(B1,1)It will return the date 1-Feb-2021.
Change the formula once again. This time change it as =edate(B1,2) to get the date 1-Mar-2021.
Here is the syntax of the function EDATE.
EDATE(start_date, [months])You May Like: How to Utilise Google Sheets Date Functions [Complete Guide]
From these three tiny formulas, we can understand one thing!
What’s that?
We can use the second argument, i.e., months, in EDATE to increment the start_date by ‘n’ months.
Instead of feeding 0, 1, 2, and so on manually, we can use the SEQUENCE function.
SEQUENCE(12,1,0)The above SEQUENCE formula will return the numbers from 0 to 11, which feeds months in EDATE.
Sequence Syntax: SEQUENCE(rows, [columns], [start], [step])
So the EDATE will generate a sequence of 12 months in chronological order.
Additionally, with the formulas in cells C3 and D3, we have used the function ArrayFormula as EDATE is non-array.
To get the sequence of end of the months in Google Sheets (D3 formula), we have additionally used the function EOMOTH.
It simply converts the EDATE outputs to end-of-the-month dates.
Result in a Row (Horizontally)
In the beginning, we have discussed the flexibility of SEQUENCE. It is high time to test that.
To generate a sequence of months or end of the months horizontally in Google Sheets, do as follows.
Here is our already existing SEQUENCE formula.
SEQUENCE(12,1,0)In both the C3 and D3 formulas, change it as;
SEQUENCE(1,12,0)If you see the #REF error, make sure that you have emptied the right of the cells to it.
Sequence of Months or End of the Months in Reverse Chronological Order in Google Sheets
In this section, you are going to get the two formulas in cells H3 and I3.
Here are them.
The following formula in cell H3 returns the sequence of months in reverse chronological order.
=ArrayFormula(EDATE($G$1,SEQUENCE(12,1,0,-1)))The following formula in cell I3 returns the sequence of end of the months in reverse chronological order.
=ArrayFormula(eomonth(EDATE($G$1,SEQUENCE(12,1,0,-1)),0))The above set of two formulas is similar to our first set of two formulas in cells C3 and D3.
The only change is the inclusion of the step value in SEQUENCE, which is -1. It helps the formula to return negative sequence numbers from 0, -1 to -11.
To change the orientation of the result, please follow the steps under the ‘Formula Explanation’ section above.
That’s all. Thanks for the stay. Enjoy!
