Function Problems

Calculate Days per Month Within a Date Range in Excel

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Calculate how many days of a start-to-end date range fall within each month.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A user enters start and end dates and wants to distribute the covered days across monthly columns.
Observed behavior
Each affected month should show the number of days covered by the date range, including the start and end dates.
Before you start

Enter valid Excel dates without time values in the start and end columns, and make sure each start date is no later than its end date. Use actual first-of-month dates for the column headings, even if you format them to display only the month name.

Solution 1Recommended

Calculate the monthly overlap with an Excel formula

Use this formula to count inclusive days in each month for one or more date ranges.

1
Enter the date range

Enter the start date in A2 and the end date in B2. For example, enter 1/30/2023 in A2 and 4/5/2023 in B2.

2
Create monthly headings

Enter 1/1/2023 in C1. In D1, enter =EDATE(C1,1) and drag the fill handle to the right through April. Optionally, select the headings and use Format Cells > Custom > mmm to display abbreviated month names.

3
Calculate the overlap

In C2, enter =MAX(0,MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1)+1). The formula counts both the start and end dates and returns zero for months outside the range.

4
Fill across and down

Drag the fill handle from C2 across the monthly columns. For additional ranges, enter their start and end dates in columns A and B, then fill the formulas down. Use a formula such as =SUM(C2:C10) below the results to total a month's days across rows.

Calculate the monthly overlap with an Excel formula
Check the example: For January 30 through April 5, 2023, the monthly results are 2, 28, 31, and 5 days. Summing multiple ranges counts each range separately, including any overlapping days.
Calculate monthly date overlaps

Calculate Days per Month in WPS Spreadsheets

Use monthly date headings and an overlap formula in WPS Spreadsheets to distribute a date range across months.

  1. 1. Enter the dates: Open WPS Spreadsheets and enter a start date in A2 and an end date in B2.
  2. 2. Add month headings: Enter the first day of the starting month in C1, then enter =EDATE(C1,1) in D1 and drag the fill handle across.
  3. 3. Enter the formula: In C2, enter =MAX(0,MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1)+1).
  4. 4. Fill and review: Drag the fill handle across the monthly columns and down for any additional date ranges. Check that each result cell displays a number.
Count inclusive days in each month with spreadsheet formulasFill formulas across months and down multiple date rangesOpen and save Microsoft Excel XLSX filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula include both the start and end dates?

Yes. The +1 at the end of the overlap calculation includes both dates. For a range contained in one day, the corresponding month returns 1.

Why does a month outside the date range show zero?

The outer MAX(0,...) prevents a negative result when the date range and the month do not overlap.

Can I display only month names in the headings?

Yes. Select the heading cells and use Format Cells > Custom > mmm. Keep the underlying values as first-of-month dates so the formula can calculate correctly.

How do I total the monthly days for several date ranges?

Enter each range on a separate row, fill the overlap formula down, and use SUM below each month's results, such as =SUM(C2:C10). Overlapping ranges are counted separately.

Will the formula account for leap years?

Yes. EOMONTH finds the actual last day of each month, including February 29 in a leap year.