How to Get a Sequence of Months in Google Sheets

Are you tired of manually creating a sequence of months or end-of-the-month dates in Google Sheets? Well, fear not! In this article, I will show you an easy and flexible way to accomplish this task using the SEQUENCE function in combination with date functions. Trust me, it’s a game-changer!

Advantages of Using SEQUENCE over ROW

Before we dive into the details, let me explain why using the SEQUENCE function is advantageous. By generating a sequence of months in Google Sheets using SEQUENCE, you can easily modify it for various purposes. Here are the main advantages:

1. Change the orientation of the result from row to columns or vice versa without using TRANSPOSE.
2. Generate a sequence of months in descending (reverse chronological) order without using SORT.

Now that you know the benefits, let’s get started with the formulas!

Sequence of Months or End of the Months in Chronological Order

Result in a Column (Vertically)

To generate a sequence of months in a column (vertically), use the following formula in cell C3:

=ArrayFormula(EDATE($B$1,SEQUENCE(12,1,0)))

This formula will generate the sequence of months in the range C3:C14.

To return the sequence of the end of the months, use the following formula in cell D3:

=ArrayFormula(eomonth(EDATE($B$1,SEQUENCE(12,1,0)),0))

Both formulas take the input (start date) from cell B1 and generate the desired result.

Formula Explanation

Let’s break down the first formula. The second one is a breeze to understand once we grasp the concept.

1. Enter the following formula in any blank cell in your Google Sheets (remember to insert the date 1-Jan-21 in cell B1):

=edate(B1,0)

It will return the date 1-Jan-21.

2. Change the formula as follows:

=edate(B1,1)

It will return the date 1-Feb-2021.

3. Change the formula once again, this time using the formula =edate(B1,2) to get the date 1-Mar-2021.

From these three tiny formulas, we can understand one crucial point: we can use the second argument, i.e., months, in the EDATE function to increment the start_date by ‘n’ months. Instead of manually inputting 0, 1, 2, and so on, we can use the SEQUENCE function. The SEQUENCE formula SEQUENCE(12,1,0) will return numbers from 0 to 11, which will be used as months in EDATE.

Additionally, to handle non-array formulas used in ArrayFormula, we have used the function EOMOTH to convert the EDATE outputs to end-of-the-month dates.

Result in a Row (Horizontally)

Now, let’s explore the flexibility of SEQUENCE by generating a sequence of months or end-of-the-month dates horizontally in Google Sheets. Follow these steps:

1. Use the existing SEQUENCE formula:

SEQUENCE(12,1,0)

2. In both the C3 and D3 formulas, modify it to:

SEQUENCE(1,12,0)

If you see a #REF error, ensure that you have cleared the cells to the right of it.

Sequence of Months or End of the Months in Reverse Chronological Order

In this section, we will obtain formulas in cells H3 and I3 that generate the sequence of months or end-of-the-month dates in reverse chronological order.

Here are the formulas:

• Formula in cell H3 (sequence of months in reverse chronological order):

=ArrayFormula(EDATE($G$1,SEQUENCE(12,1,0,-1)))

• Formula in cell I3 (sequence of end-of-the-month dates in reverse chronological order):

=ArrayFormula(eomonth(EDATE($G$1,SEQUENCE(12,1,0,-1)),0))

The above formulas are similar to the first set of formulas in cells C3 and D3, with the only difference being the inclusion of the step value in SEQUENCE, which is -1. This helps the formula return negative sequence numbers from 0 to -11.

To change the orientation of the result, simply follow the steps mentioned in the ‘Formula Explanation’ section above.

That’s it! Now you know how to effortlessly generate a sequence of months or end-of-the-month dates in Google Sheets. Happy spreadsheet-ing!

Check out Crawlan.com for more amazing tips and tricks!

Resources

• Array Formula to Generate Bimonthly Dates in Google Sheets.
• Incrementing Duplicate Dates by Month or Day in Google Sheets.
• Fill a Column with Sequence of Decimals in Google Sheets.
• Sequence Numbering in Merged Cells In Google Sheets.
• Backward Sequence Numbering in Google Sheets.