Google Sheets offers several built-in functions like DAYS, DATEDIF, NETWORKDAYS, and NETWORKDAYS.INTL to calculate the number of days between two dates. However, none of them can directly calculate the number of days in each month between two dates. They only return the total number of days, not a month-wise breakdown.

In this guide, you’ll get a custom Google Sheets formula that returns the exact number of days per month between any two dates. You can also customize it to include or exclude weekends and holidays.

Why You May Need This

This method is especially useful for real-world scenarios such as:

• Payroll and salary period calculations
• Attendance or leave tracking
• Rent or utility billing cycles
• Project or resource allocation by month

By the end, you’ll have a flexible, automated solution that works across different date ranges and can easily be adapted for working-day or total-day calculations.

Tip:
If you’re working with hotel stays or bookings, check out my related guide —
Calculate the Number of Nights in Each Month in Google Sheets.

If you manage travel allowances where start and end days are treated separately, see this one —
Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets.

Formula to Calculate Days in Each Month Between Two Dates

Assume you have the following start and end dates in columns A and B:

If you want to exclude holidays, prepare a holiday list in column C.
Let’s ignore that for now to keep things simple.

In column E, we’ll apply the formula to calculate month-wise days between the start and end dates.

Google Sheets Formula

Since Google Sheets lacks a direct function for this, we must use an advanced, single-cell ArrayFormula with the LET and LAMBDA functions. This allows the formula to instantly process your entire date range (all rows) and dynamically generate the monthly headers.

Use this formula in cell E1:

=ArrayFormula(LET(
  start, A2:A6, 
  end, B2:B6, 
  wEnd, "0000000", 
  hDays, 0,
  header, EDATE(DATE(YEAR(MIN(start))-1, 12, 1), SEQUENCE(1, (YEAR(MAX(end))-YEAR(MIN(start))+1)*12)), 
  fnl, MAP(start, end, LAMBDA(x, y, 
    LET(
      bom, EOMONTH(x, -1)+1, 
      rawDts, VSTACK(x, y, EDATE(bom, SEQUENCE(DATEDIF(bom, EOMONTH(y, 0), "M")))), 
      dts, TOCOL(SORTN(rawDts, 9^9, 2, EOMONTH(rawDts, 0), 1), 3), 
      cntDts, COUNT(dts), 
      XLOOKUP(
        header, 
        EOMONTH(dts, -1)+1, 
        IF(cntDts=1, NETWORKDAYS.INTL(x, y, wEnd, hDays), 
          IF(dts=MIN(dts), NETWORKDAYS.INTL(dts, EOMONTH(dts, 0), wEnd, hDays), 
            IF(dts=MAX(dts), NETWORKDAYS.INTL(EOMONTH(dts, -1)+1, dts, wEnd, hDays), 
              NETWORKDAYS.INTL(EOMONTH(dts, -1)+1, EOMONTH(dts, 0), wEnd, hDays)
            )
          )
        ),
        "-"
      )
    )
  )), VSTACK(header, fnl)
))

This Google Sheets formula returns a header row with month start dates, followed by the number of days that fall in each month for every start–end pair.

Formatting Tip

Select the header row → Format > Number > Custom Number Format → enter
MMM-YYYY
to display months as Jan-2025, Feb-2025, and so on.

Exclude Weekends from Month-Wise Day Calculation

By default, the formula counts all days (including weekends).
Weekends are defined by this code:

"0000000"

Each digit represents a day of the week, starting from Monday to Sunday:

• 0 = Working day
• 1 = Weekend

To exclude Saturday and Sunday, modify this part to:

"0000011"

This tells Google Sheets to treat Saturday and Sunday as non-working days.

Exclude Holidays from Month-Wise Day Calculation

If you want to exclude holidays, list them in column D and replace this part in the formula:

hDays, 0

with:

hDays, D2:D12

Example: U.S. Federal Holidays

The formula will automatically exclude holidays that fall within each start–end range.

Formula Explanation

This is the fun part! If you’re curious about what happens behind the scenes, this breakdown will help you understand how the Google Sheets month-wise days formula works.

Step 1: Define Core Variables

start, A2:A6
end, B2:B6
wEnd, "0000000"
hDays, 0

These define your start and end dates, weekend pattern, and holiday range.

We use the LET function to create “variables” (start, end, wEnd, hDays, etc.) inside the formula. This makes a long formula easier to read, avoids repeated calculations, and allows for easier customization later.

Step 2: Generate the Header Row

header, EDATE(DATE(YEAR(MIN(start))-1, 12, 1), SEQUENCE(1, (YEAR(MAX(end))-YEAR(MIN(start))+1)*12))

Generates a header row with month-start dates spanning from the earliest start year to the latest end year (January–December).

Step 3: Calculate Days for Each Date Pair

a. Determine Beginning of the Month (BOM)

bom, EOMONTH(x, -1)+1

Returns the first day of the month for the start date.

b. Generate Raw Dates for Each Month

rawDts, VSTACK(x, y, EDATE(bom, SEQUENCE(DATEDIF(bom, EOMONTH(y, 0), "M"))))

Creates a vertical list:

• Start date
• End date
• All month-start dates in between

Example:
Start = 10/08/2025, End = 02/10/2025
Result:
10/08/2025
02/10/2025
01/09/2025
01/10/2025

c. Deduplicate and Sort Month-End Dates

dts, TOCOL(SORTN(rawDts, 9^9, 2, EOMONTH(rawDts, 0), 1), 3)

It removes duplicates based on the end-of-month dates and orders them chronologically.
Result:
10/08/2025
01/09/2025
02/10/2025

Step 4: Calculate Working Days per Month in Google Sheets

IF(
  cntDts=1, NETWORKDAYS.INTL(x, y, wEnd, hDays),
  IF(dts=MIN(dts), NETWORKDAYS.INTL(dts, EOMONTH(dts, 0), wEnd, hDays),
  IF(dts=MAX(dts), NETWORKDAYS.INTL(EOMONTH(dts, -1)+1, dts, wEnd, hDays),
  NETWORKDAYS.INTL(EOMONTH(dts, -1)+1, EOMONTH(dts, 0), wEnd, hDays))))

Explanation:

• If both dates are in the same month → return total working days between them.
• If it’s the first month → count from the start date to the end of that month.
• If it’s the last month → count from the month start to the end date.
• Otherwise → count all working days in that full month.

Step 5: Align Results with Header Columns

XLOOKUP(header, EOMONTH(dts, -1)+1, [calculated working days], "-")

Aligns each calculated value under the correct month column.

Step 6: Apply to Each Row

MAP(start, end, LAMBDA(x, y, ...))

The MAP function iterates (loops) over each start and end date pair, while LAMBDA defines the calculation that occurs for each pair. This is the “magic” that allows the formula to work across the entire column range (A2:A6 and B2:B6) from a single cell (e.g., E1).

✅ In summary:

• Builds a month-wise header automatically.
• Calculates how many days fall in each month.
• Adjusts for weekends and holidays using NETWORKDAYS.INTL.
• Returns a clean, dynamic table for easy reporting or analysis.

FAQs

1. Can I calculate days in each month between two dates without using a custom formula?
No, Google Sheets doesn’t provide a built-in function for month-wise day breakdowns. You’ll need a custom formula like the one in this guide.

2. How do I include or exclude weekends in my calculation?
You can control this by changing the weekend code inside the NETWORKDAYS.INTL function (e.g., "0000011" excludes Saturday and Sunday).

3. Can I exclude holidays automatically?
Yes, just list your holiday dates in a column (e.g., D2:D12) and reference that range in the formula parameter hDays.

4. Will this formula work if the start and end dates span multiple years?
Absolutely. The formula dynamically creates a month header covering all months between the earliest start and latest end year.

Wrapping Up

With this custom Google Sheets formula, you can easily calculate days in each month between two dates — including or excluding weekends and holidays.

It’s a powerful, reusable method for monthly time tracking, payroll management, project planning, or billing cycles in Google Sheets.

Sample Sheet