Formula Errors

How to Count Days in Each Month Between Two Dates in Excel

Aamir Naveed Akram Oct 9, 2026 869 views

Question details

The user needs an Excel formula to calculate the exact number of days that fall into each month between a given start date and end date.

How to Count Days in Each Month Between Two Dates in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Project management, billing, or resource tracking where a specific time frame spans multiple months and requires accurate day counts per month.
Observed behavior: The goal state is a formula that accurately apportions days across partial starting months, full middle months, partial ending months, and automatically accounts for leap years.

Before you start

Ensure your start and end dates are formatted as valid Excel dates. You also need a row of month-start dates (e.g., the 1st of each month) to use as column headers for the formula to reference.

Solution 1Recommended

Use the MAX, MIN, and EOMONTH Formula

This robust formula calculates the overlap between your specific date range and a given month, handling partial months and preventing negative numbers.

This formula works by finding the later of two start dates (the project start date or the month's first day) and the earlier of two end dates (the project end date or the month's last day). It subtracts the calculated start from the calculated end and adds 1 to make it inclusive. The outer MAX function ensures that months completely outside the date range return 0 instead of a negative number.

Set up your date inputs

Place your actual Start Date in cell K6 and your End Date in cell L6.

Create month headers

In row 5 (starting at M5), enter the 1st day of each month you want to track. For example, enter 5/1/2024 in M5, 6/1/2024 in N5, and so on. You can format these cells to display only the month and year.

Enter the formula

In cell M6, directly below your first month header, type the following formula: =MAX(MIN(EOMONTH(M$5,0),$L6)-MAX(M$5,$K6)+1,0)

Fill the formula across and down

Press Enter. Select cell M6, grab the fill handle in the bottom-right corner, and drag it to the right to calculate the days for subsequent months. If you have multiple date ranges in rows below, drag the fill handle down.

How EOMONTH Helps: The EOMONTH(M$5,0) portion automatically finds the last day of the month specified in M5. This dynamically handles varying month lengths (28, 30, or 31 days) and automatically accounts for leap years without manual adjustments.

Free Spreadsheet Software

Easily Calculate Date Ranges Across Months in WPS Spreadsheet

WPS Spreadsheet offers full native support for advanced date and time functions like EOMONTH, MAX, and MIN. You can seamlessly calculate complex project timelines and financial schedules with a familiar interface.

1. Launch WPS Spreadsheet: Open WPS Office and create a new Spreadsheet document.
2. Input Your Data: Enter your start dates, end dates, and monthly header dates just as you would in Excel.
3. Apply the Formula: Paste the =MAX(MIN(EOMONTH(M$5,0),$L6)-MAX(M$5,$K6)+1,0) formula into your target cell.
4. Drag to Autofill: Use the intuitive fill handle to drag and apply the formula across all required months and project rows.

100% compatible with Microsoft Excel date formulas and formatting.Supports advanced functions like EOMONTH natively.Free, lightweight, and incredibly fast for complex data analysis.Familiar user interface makes transitioning from Excel seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning negative numbers for some months?

If you don't wrap the core calculation in the outer =MAX(..., 0) function, months that fall completely outside your start and end date range will result in negative numbers. The outer MAX function forces any negative results to 0.

How does the formula handle leap years?

The EOMONTH function built into the formula automatically accounts for leap years. When you reference a date in February during a leap year, EOMONTH(date, 0) accurately identifies the last day of the month as February 29th.

What if I only want to count working days (exclude weekends)?

To exclude weekends, you need to replace the standard subtraction logic with the NETWORKDAYS function. The formula would look like this: =MAX(0, NETWORKDAYS(MAX($K6, M$5), MIN($L6, EOMONTH(M$5,0))))

Why do I need the +1 in the formula?

The +1 is required for inclusive counting. If you subtract January 1st from January 5th, the mathematical result is 4. However, there are actually 5 inclusive days in that range (the 1st, 2nd, 3rd, 4th, and 5th), so +1 corrects the count.

Excel & Data Problems