# How to calculate how many days of a date range fall in each month in Excel

Overlap the range with each month: `=MAX(0,MIN($B$2,EOMONTH(D2,0))-MAX($B$1,D2)+1)`, with the month's first day in D2.

**Excel:** `=MAX(0,MIN($B$2,EOMONTH(D2,0))-MAX($B$1,D2)+1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Start | 2026-01-20 |  | Month | Days |
| End | 2026-04-10 |  | 2026-01-01 |  |
|  |  |  | 2026-02-01 |  |
|  |  |  | 2026-03-01 |  |
|  |  |  | 2026-04-01 |  |
|  |  |  | 2026-05-01 |  |

Formula in E2, filled to E6: `=MAX(0,MIN($B$2,EOMONTH(D2,0))-MAX($B$1,D2)+1)`

Result: 12; 28; 31; 10; 0

## How it works

For each month, the overlap starts at the later of the range start and the month start, and ends at the earlier of the range end and the month end. Their difference plus 1 is the number of days in that month, and `MAX(0, …)` returns 0 for months outside the range. The days add up to 81, the length of the whole range.

## Other ways

- `=MAX(0,NETWORKDAYS(MAX($B$1,D2),MIN($B$2,EOMONTH(D2,0))))`: Working days in each month instead of calendar days. (Counts weekdays only.)

## Common mistakes

- Without the +1 every month is a day short, because both ends of the overlap count.
- Column D must hold the first day of each month. Use `EOMONTH(D2,-1)+1` in place of D2 if it might not.

## Questions

**How do I split an amount across the months?**

Share it by days: `=amount*E2/SUM($E$2:$E$6)`.

**How do I check the total?**

`=SUM(E2:E6)` should equal `B2-B1+1`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/days-in-each-month-of-a-date-range