Increment Months Between Two Given Dates in Google Sheets

If you have a start date in cell A2 and an end date in cell B2, you might wonder how to increment the months between these two dates. This is a common requirement for many spreadsheet users.

To achieve this, we can utilize a combination of the DATEDIF, SEQUENCE, EDATE, and ARRAYFORMULA functions.

Example Setup

In the following example, the start date is in cell A2 (05/10/2017), and the end date is in cell B2 (11/06/2018). The resulting month sequence would be:

05/10/2017
05/11/2017
05/12/2017
05/01/2018
05/02/2018
05/03/2018
05/04/2018
05/05/2018
05/06/2018

If you prefer to list the start dates of each month, you can enter 01/10/2017 and 01/06/2018 in cells A2 and B2, respectively.

Formula to Increment Months Between Two Given Dates

Formula:

=ARRAYFORMULA(
   EDATE(
      A2, 
      SEQUENCE(DATEDIF(A2, B2, "M") + 1, 1, 0)
   )
)

Explanation of the Formula

  • DATEDIF(A2, B2, "M"): This function calculates the number of complete months between the two dates in A2 and B2.
  • SEQUENCE(…): This function generates a sequence of numbers from 0 to n, where n is the value returned by the DATEDIF function. The addition of + 1 ensures that the last month is included in the output.
  • EDATE(A2, …): This function returns a date that is a specified number of months before or after the start date (A2). Here, it uses the sequence of numbers generated to increment the months accordingly.

Since the months argument (with the syntax EDATE(start_date, [months])) in the EDATE function consists of multiple values (the sequence), we must use ARRAYFORMULA to output the entire sequence of dates.

Additional Tips

To generate the start dates of each month, you can enter the first day of the month (e.g., 01/10/2017 for October) in cell A2 and the first day of the last month (e.g., 01/06/2018 for June) in cell B2. However, this is not the only way to achieve the same result.

To populate the month-end dates instead, use the following formula:

=ARRAYFORMULA(
   EOMONTH(
      EDATE(
         A2, 
         SEQUENCE(DATEDIF(A2, B2, "M") + 1, 1, 0)
      ), 0
   )
)

If you wish to calculate the start date of each month, use this formula:

=ARRAYFORMULA(
   EOMONTH(
      EDATE(
         A2, 
         SEQUENCE(DATEDIF(A2, B2, "M") + 1, 1, 0)
      ), -1
   )+1
)

Resources for Further Reading

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.