Prorate a Date Range Across Months in Excel

Marrable Workspace ran October's desk licences and invoiced £12,325.00. The month is worth £10,221.78. Nothing errored, every tenant on the sheet was real, every monthly fee was right, and six of them paid for days they did not have.

The run charged a whole month to any licence that touched October. A desk let from the 26th was invoiced the same £980.00 as a desk let all year, and a licence that ended on the 9th was invoiced to the 31st.

The second fault points the other way, and nobody queried it because the tenant never received an invoice. DL-153 is open-ended, so its end date is blank. A blank cell is zero, zero as a date is 1900, and a contract that ended in 1900 bills nothing.

A month is not a flag on a contract. It is the overlap between two date ranges, and an overlap is arithmetic you can write in one cell.

What you need. MIN, MAX, DAY, EOMONTH, COUNTIFS and SUMPRODUCT work in every version of Excel this century and in Google Sheets.

LET, MAP and LAMBDA need Microsoft 365 or Excel 2021 and later, and everything they do here can be done in a helper column instead. EOMONTH, EDATE and NETWORKDAYS needed the Analysis ToolPak add-in in Excel 2003 and earlier.

01Twelve Desks, One Month, Two Faults

Column C is the start date, column D the end date and column E the monthly fee. Column F is the days of October each licence occupies, column G is what the run invoiced, and column H is what the licence is worth that month. The month itself sits in J1 and J2.

Four licences ran the whole of October and agree: DL-118, DL-124, DL-158 and DL-165. DL-170 starts on 2 November and was correctly left out. The other seven rows are each wrong, by a different amount.

DL-150 is the row that makes the fault plain. It is a four-day stop-gap desk at £310.00 a month, and it was invoiced £310.00 for four days of occupancy worth £40.00.

02The Test Was Right; the Amount Was Not

October's run was one formula, filled down column G: =IF(AND(C2<=$J$2,D2>=$J$1),E2,0). It asks whether the licence touches October and bills the whole fee when it does.

The test half is correct and worth keeping. Two date ranges overlap when each one starts on or before the other ends. For a contract and a month that is C2<=$J$2 and D2>=$J$1, and both halves are load-bearing: drop the first and November's contracts get billed, drop the second and September's do.

What the formula cannot do is answer in days. AND returns TRUE or FALSE, and TRUE is not an amount — so when somebody needed a figure, the monthly fee was the only number to hand.

03The Overlap Formula: MIN, MAX and the +1

One formula gives the days a contract occupies a month, and it is the one to learn:

=MAX(0,MIN(D2,$J$2)-MAX(C2,$J$1)+1)

MIN(D2,$J$2) is the earlier of the contract's end and the month's end: whichever stops the billing first. MAX(C2,$J$1) is the later of the two starts: whichever begins it. Between them they clip the contract to the month.

Walk DL-150 through it. The licence runs 6 to 9 October, so MIN returns 9 October and MAX returns 6 October:

MIN(09/10/2026, 31/10/2026)  = 09/10/2026
MAX(06/10/2026, 01/10/2026)  = 06/10/2026
09/10 - 06/10 + 1            = 4 days

The +1 is not a fudge, it is the billing convention. A licence that starts and ends on the same day is charged for one day, not nought, so both end dates sit inside the period.

Drop it and every row in the file is a day short, consistently, which is the kind of error that reconciles against itself for years.

The outer MAX(0,...) handles the rows that are not in the month at all. DL-170 starts on 2 November, so clipping gives an end before the start, the subtraction returns a negative, and without the guard the sheet would credit the tenant for minus two days.

04Pro Rata by Days, and Which Denominator

Days become money by dividing by the length of the month. With the overlap days in column F:

=ROUND(E2*F2/DAY($J$2),2)

DAY($J$2) reads the day number off the month's last date, so October returns 31, November 30, and February 2028 returns 29. That beats typing 30 or 365/12, because the formula then follows the calendar instead of an assumption about it.

There is more than one defensible basis, though, and they disagree. DL-136 occupies six days of October at £980.00 a month:

The basis is a term of the contract, not a formula preference. Pick the one the licence states, put it in a named cell, and the monthly figures will reconcile with what the tenant was told they were buying.

Round once, on the row, to pence. ROUND(...,2) keeps the invoice and the ledger on the same number. Leave it out and the row shares carry fractions of a penny that turn into a control total a penny or two adrift, with no row to blame it on.

05A Blank End Date Bills Nothing

DL-153 has no end date because it has no end: it runs until somebody gives notice. The cell is empty, and an empty cell in arithmetic is zero.

So MIN(D2,$J$2) compares the month end against zero and returns zero, which as a date is 0 January 1900. The overlap comes out around minus 46,000 days, MAX(0,...) floors it at nought, and the open-ended contract bills nothing. It billed nothing in September either: £2,300.00 between the two months.

Guard the end date where you read it, rather than patching the number afterwards:

=MAX(0,MIN(IF(D2="",$J$2,D2),$J$2)-MAX(C2,$J$1)+1)

An open-ended licence that is still running has, for this month's purposes, an end date of the month end. IF(D2="",$J$2,D2) says exactly that, and it keeps working in every later month without anybody editing the sheet.

The alternative is to ban the blank: put a far-future date such as 31 December 2099 in the column, and use data validation to require a date. Either answer is fine; what cannot stand is a column where blank sometimes means "forever" and sometimes means "nobody has filled this in".

06MIN and MAX Aggregate, So the Rows Need a Column

The tempting next step is to skip the helper column and total the month in one cell. It does not work, and it fails quietly:

=SUMPRODUCT(E2:E13*(MIN(D2:D13,$J$2)-MAX(C2:C13,$J$1)+1)/DAY($J$2))

MIN and MAX are aggregates. Handed a whole column they return one number for the lot: MIN(D2:D13,$J$2) is the earliest end date in the file — zero, thanks to DL-153 — and MAX(C2:C13,$J$1) is DL-170's 2 November. Every row then gets the same nonsense day count.

Two things fix it. The first is the helper column, which is also the version somebody else can audit:

=SUMPRODUCT(E2:E13,F2:F13)/DAY($J$2)

The second is MAP, which applies the row formula to each row in turn and totals the result, with no extra column on the sheet:

=SUM(MAP(C2:C13,D2:D13,E2:E13,LAMBDA(s,e,f,
  f*MAX(0,MIN(IF(e="",$J$2,e),$J$2)-MAX(s,$J$1)+1)/DAY($J$2))))

Any formula that wraps a whole column in MIN or MAX and expects one answer per row is aggregating the file by accident, and nothing in the result says so. The number comes back formatted as money and sits in the cell looking exactly like a total.

07One Formula Across a Grid of Months

A single month is one column. A year is the same formula with the months across the top, which is where mixed references earn their keep. Put the first of each month in M1 to X1, then in M2:

=MAX(0,MIN($D2,EOMONTH(M$1,0))-MAX($C2,M$1)+1)

$C2 and $D2 lock the column and let the row travel; M$1 locks the row and lets the column travel. Fill the block right and down, and every contract is split across every month it touches, with nought in the months it does not.

For money rather than days, wrap it the same way and let EOMONTH supply both the month end and the length of the month:

=ROUND($E2*MAX(0,MIN($D2,EOMONTH(M$1,0))-MAX($C2,M$1)+1)/DAY(EOMONTH(M$1,0)),2)

The grid brings its own check, which is the reason to build it this way. A contract's days across the whole grid should add up to the contract's own length, so =SUM(M2:X2) and =D2-C2+1 agree on every row that starts and ends inside the window.

08Counting What Is Live, Not What Started

Occupancy went the same way as the money. The dashboard counted contracts whose start date fell inside the month:

=COUNTIFS($C$2:$C$13,">="&$J$1,$C$2:$C$13,"<="&$J$2)

That returns 4, and it is a true figure about a different question: four licences were signed in October. Eleven were occupied at some point during it, and the two numbers were never meant to match.

Live contracts are an overlap test, not a date filter:

=SUMPRODUCT(($C$2:$C$13<=$J$2)*(IF($D$2:$D$13="",$J$2,$D$2:$D$13)>=$J$1))

Unguarded, that count returns 10 rather than 11, because the blank end date fails the test the same way it failed the billing. The IF is the same guard as in section 5, and it belongs in every formula that reads the end date.

If what you want is desks rather than contracts, average the days instead. The twelve licences occupy 230 days of a 31-day month, so =SUM(F2:F13)/DAY($J$2) returns 7.42 desks in use on average across October.

09Working Days Instead of Calendar Days

Day-rate work — contractors, plant hire, interim cover — is billed on working days, and then the denominator changes too. NETWORKDAYS counts both end dates, so there is no +1 to add:

=MAX(0,NETWORKDAYS(MAX(C2,$J$1),MIN(IF(D2="",$J$2,D2),$J$2),Holidays))

October 2026 holds 22 working days against 31 calendar ones, and the two bases move the mid-month rows apart. A spell that spans two weekends loses days; one that sits Monday to Friday gains share of the month.

DL-150 is the clearest case. Four days that happen to fall Tuesday to Friday are four calendar days and four working days, and yet 4/22 of a month is a bigger share than 4/31 — £16.36 bigger on this licence.

10When Whole Months Are the Right Answer

Not every contract is prorated. Anniversary billing charges a full month from the start date, so a licence beginning on 12 October bills on the 12th of every month, and prorating it is a wrong answer rather than a refinement.

Count months rather than days when that is the deal. =DATEDIF(C2,D2+1,"m") gives whole months elapsed, and months touched is arithmetic on the parts:

=(YEAR(D2)-YEAR(C2))*12+MONTH(D2)-MONTH(C2)+1

For the next billing date, EDATE handles the month-end problem that adding 30 days cannot. =EDATE(C2,1) on 31 January returns 28 February, and on 31 October returns 30 November, because it clamps to the end of the shorter month.

The convention is a term of the contract, so the two populations should not share a formula. Keep a basis column in K — Pro rata or Whole month — and switch on it: =IF(K2="Whole month",E2,ROUND(E2*F2/DAY($J$2),2)).

11Two Bookings, One Desk

The same overlap test finds double-booked resources, which is the other thing a pair of date columns is hiding. For each row, count the rows on the same desk whose dates overlap it:

=SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13<=D2)*($D$2:$D$13>=C2))>1

Every row matches itself, so the count is 1 for a clean booking and 2 or more for a clash. On this sheet it returns TRUE twice, both on desk D-04: DL-147 runs to 23 October and DL-161 starts on the 19th.

That is five days, 19 to 23 October, with one desk sold to two tenants. =MAX(0,MIN(D7,D11)-MAX(C7,C11)+1) is the overlap formula again, pointed at two contracts instead of a contract and a month, and it returns 5.

Blank end dates break this check the way they broke the billing, so guard them here too: IF($D$2:$D$13="",$J$2,$D$2:$D$13) inside the third test. An open-ended contract overlaps everything after its start, which is exactly what a clash report should say.

12The Checks That Catch It Before Invoicing

Five cells above the table would have caught October before it went out, and each one is a question with an answer you already know.

1. Does the month total two ways? =SUM(H2:H13) returns £10,221.78 and =SUMPRODUCT(E2:E13,F2:F13)/DAY($J$2) returns £10,221.77. A penny apart is the rounding you chose on the rows; pounds apart is a row where the days and the money disagree.
2. Can any row exceed the month? =SUMPRODUCT(--(F2:F13>DAY($J$2))) must be nought. Anything else means a date that should have been clipped was not.
3. Is any end date before its start? =SUMPRODUCT((D2:D13<>"")*(D2:D13<C2:C13)) must be nought, and it is the check that finds a mistyped year.
4. Are the dates dates? =SUMPRODUCT(--ISTEXT(C2:C13))+SUMPRODUCT(--ISTEXT(D2:D13)) must be nought. Text sorts above every real date, so MIN picks the text and the row bills a whole month.
5. How many blanks? =SUMPRODUCT(--(D2:D13="")) returns 1 here, which is DL-153 and is deliberate. The figure is only safe when you know what it is.

The full-month rows are the ones to reconcile against the fee. Five contracts span the whole of October, =COUNTIFS(F2:F13,DAY($J$2)) confirms it, and each of those five should bill its fee exactly. One of them coming out at £1,045.16 instead of £1,620.00 means the clipping has gone wrong in the other direction.

13Ten Things That Bite

1. A blank end date is zero, not forever. MIN picks it, the overlap goes negative, and the contract bills nothing at all.
2. The +1 is a billing convention. Leave it out and every row is a day short, consistently and invisibly.
3. MIN and MAX aggregate. Over a column they return one number for the whole file, not one per row.
4. A start-date filter is not a liveness test. COUNTIFS on the start date answers "signed this month", which is a different number.
5. DAY($J$2) is the month's length. Hard-coding 30 overcharges eleven months of the year on a 30-day contract.
6. Text dates win every comparison. One "01/10/2026" typed as text beats every real date in a MIN, and the row bills a full month.
7. Round once, to pence, on the row. Unrounded shares reconcile a penny or two out and nobody can find which row did it.
8. Mixed references make the grid. $C2 and M$1, not C2 and M1, or the fill scatters across the block.
9. NETWORKDAYS already counts both ends. Adding +1 to it bills a day that does not exist.
10. Prorating a whole-month contract is also an error. The basis belongs in a column, not in an assumption.

14Mini Exercises

1. Rebuild the broken column. Write =IF(AND(C2<=$J$2,D2>=$J$1),E2,0) down column G of the example sheet and confirm it totals £12,325.00. That is the number that went out.
2. Clip it. Put =MAX(0,MIN(D2,$J$2)-MAX(C2,$J$1)+1) in column F and =ROUND(E2*F2/DAY($J$2),2) in column H, then reconcile the total to £10,221.78.
3. Find the open-ended row. Note what DL-153 bills before you guard the blank, then add IF(D2="",$J$2,D2) and watch £1,150.00 arrive.
4. Count three ways. Work out licences signed, licences live and average desks for October, and check you get 4, 11 and 7.42.
5. Catch the clash. Add the desk-overlap column, find the pair on D-04, and work out how many days of October were sold twice.

What to Take Away

The sheet was not broken. It asked a true-or-false question, got a true answer, and then used a monthly fee as though it answered "how much of this month?" — a question nobody had written down anywhere.

Writing it down takes one cell. =MAX(0,MIN(D2,$J$2)-MAX(C2,$J$1)+1) is the whole idea: clip the contract to the month at both ends, count the days inclusively, floor the result at nought. Everything else here is that formula with a different denominator or a different pair of ranges.

And guard the blank. An empty end date is the most expensive cell in a contract table, because it reads as a date in 1900 and it fails in the direction nobody complains about.