Month and Day Breakdown from Two Dates in Google Sheets

We can use the QUERY function to get the month and day breakdown from a start and end date in Google Sheets. In that breakdown, you can also exclude specific weekends!

For example, suppose you have a project start date and end date from 2024-02-15 to 2024-06-20.

The Month and Day Breakdown Including Weekends Will Be:

Feb15
Mar31
Apr30
May31
Jun20

The Month and Day Breakdown Excluding Weekends (for example, Sunday and Saturday) Will Be:

Feb11
Mar21
Apr22
May23
Jun14

How do we do that?

Here are the step-by-step instructions to get a month and day breakdown from a start and end date, including or excluding weekends, in Google Sheets.

Step 1: Expanding the Start and End Dates

For this example, we have the start date in cell A1 and the end date in cell B1. This will be the same for all formulas in this step-by-step guide.

In the first step of breaking down the start and end dates to months and days, we will use the SEQUENCE function to expand the start and end dates.

=SEQUENCE(B1-A1+1, 1, A1)

You can try this formula by inserting it in the first row of column D, provided D1:D
is empty (blank).

Date sequence from start to end date

The result will be date values.

The above formula follows the syntax SEQUENCE(rows, [columns], [start], [step]).

Where:

  • rows: B1-A1+1, which is the total number of days from the start to the end date.
  • columns: 1, which represents the number of columns in the result.
  • start: A1, the starting value, which here is the start date.

Step 2: Converting the Expanded Dates to the Beginning of the Month

We can use the EOMONTH function to convert the sequence of generated dates to the beginning of each month (BOM).

For example, for any date that falls in January 2024, the BOM will be 2024-01-01. This facilitates grouping, and additionally, we can format this to display month text, month and year text, or month number string.

Generic Formula:

=ArrayFormula(EOMONTH(sequence_of_dates, -1)+1)

What you should do here is use the formula from Step 1 for ‘sequence_of_dates’. The complete formula in cell D1 will be:

=ArrayFormula(EOMONTH(SEQUENCE(B1-A1+1, 1, A1), -1)+1)
Date sequence transformed into beginning of the month dates

This formula follows the syntax EOMONTH(start_date, months) where ‘months’ is -1, representing the end of the previous month.

We add +1 to get the BOM. This is necessary because there is no BOMONTH function in Google Sheets.

The ARRAYFORMULA function is necessary as we use EOMONTH in an array.

We can now use this data in the QUERY function to return the month and day breakdown.

Step 3: Month and Day Breakdown from Start and End Dates Including Weekends

Generic Formula:

=ArrayFormula(QUERY(data, "SELECT Col1, COUNT(Col1) GROUP BY Col1 LABEL COUNT(Col1)''", 0))

Replace ‘data’ with the formula from Step 2. Your month and day breakdown from the start and end date is now ready.

Here is the final formula:

=ArrayFormula(QUERY(EOMONTH(SEQUENCE(B1-A1+1, 1, A1), -1)+1, "SELECT Col1, COUNT(Col1) GROUP BY Col1 LABEL COUNT(Col1)'' ", 0))

This will return the months, represented by the beginning of the month in the first column, and the number of days in the second column.

Number of months and days between start and end date

To format the dates:

  • Select the dates in the first column.
  • Click on Format > Number > Custom Number Format.
  • In the provided field, enter one of the following formats and click Apply:
    • mmm (for month name abbreviation)
    • mmmm (for full month name)
    • mmm-yy (for month and year)

Here in this formula, we have used the QUERY function to run a query over the sequence of beginning-of-month dates, similar to SQL.

Syntax: QUERY(data, query, [headers])

Where the ‘query’ string is "SELECT Col1, COUNT(Col1) GROUP BY Col1 LABEL COUNT(Col1) ''"

  • SELECT Col1, COUNT(Col1) selects each unique BOM date and counts its occurrences.
  • GROUP BY Col1 groups the data by each unique BOM date.
  • LABEL COUNT(Col1) '' removes the default label for the count column.
  • 0 indicates no header row in the data.

Step 4: Month and Day Breakdown from Start and End Dates Excluding Weekends

The above steps provide a method to get a month and day breakdown from a start and end date in Google Sheets.

However, to exclude weekends from the day count, we need to use a different formula in Step 1.

Instead of returning a date sequence from the start and end dates, we want to generate a sequence of working days from the start to the end.

Replace SEQUENCE(B1-A1+1, 1, A1) in the previous formula with WORKDAY.INTL(A1-1, SEQUENCE(NETWORKDAYS.INTL(A1, B1, "0000011")), "0000011").

Month and day breakdown from start to end excluding weekends

This follows the syntax WORKDAY.INTL(start_date, num_days, [weekend], [holidays]) and returns a date after a specified number of workdays. In our formula, the specified number of workdays is represented by sequence numbers.

Where:

  • start_date: A1-1 – This is the day before the starting date of the sequence to make the starting date inclusive in the sequence.
  • num_days: SEQUENCE(NETWORKDAYS.INTL(A1, B1, "0000011")) – The NETWORKDAYS.INTL function returns the number of working days between the start and end dates. The SEQUENCE function returns sequence numbers corresponding to that.
  • weekend: "0000011" – This represents Saturday and Sunday as weekends. The 7 characters (0’s and 1’s) represent the days from Monday to Sunday, where 0 represents a working day and 1 represents a weekend.

Here is the formula to get the month and day breakdown excluding weekends in Google Sheets:

=ArrayFormula(QUERY(EOMONTH(WORKDAY.INTL(A1-1, SEQUENCE(NETWORKDAYS.INTL(A1, B1, "0000011")), "0000011"), -1)+1, "SELECT Col1, COUNT(Col1) GROUP BY Col1 LABEL COUNT(Col1)'' ", 0))

Resources

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.