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:

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

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).

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)

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.

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").

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

• Find Missing Sequential Dates in a List in Google Sheets
• Auto-Fill Sequential Dates When Value Entered in Next Column
• How to Get Sequence of Months in Google Sheets
• Creating Sequential Dates in Equally Merged Cells in Google Sheets
• How to Find Net Working Days in Google Sheets that Excluding Holidays
• Find the Number of Working and Non-Working Days in Google Sheets
• How to Populate Sequential Dates Excluding Weekends in Google Sheets
• Finding the Last 7 Working Days in Google Sheets (Array Formula)