Excel Challenge 36 : Calculate Pro-Rated Costs

What You Will Learn

Four subscription services with monthly fees and partial-month usage. Cloud Storage costs $30/month but was used for only 10 days (Apr 1-10). You need the prorated bill for each row.

The math: monthly fee ÷ 30 days = daily rate. Daily rate × days used = prorated bill.

=(B2/30)*(D2-C2+1)

Breaking it down: B2/30 = $30 ÷ 30 = $1 per day. D2-C2+1 = end date minus start date plus 1. Apr 10 minus Apr 1 = 9, plus 1 = 10 days. The +1 ensures both the start and end dates are counted. Without +1, a same-day service (Apr 5 to Apr 5) would show 0 days instead of 1. Then $1 × 10 = $10 prorated bill.

The formula assumes dates are stored as real dates in Excel. If your dates are text (like "2026-04-01"), you need to convert them with DATE, LEFT, MID, and RIGHT before doing math. The challenge solution does this conversion automatically.

Cloud Storage: $10. Premium Support: $150 × 16 ÷ 30 = $80. Virtual Host: $45 × 1 ÷ 30 = $1.50. Email Suite: $12 (full month) = $12. Total revenue: $103.50.

Monthly fee ÷ 30 × days used = prorated bill.

The audit formulas:

B9: =COUNTA(A2:A5) — total customers (4).
B10: =SUM(E2:E5) — total revenue to collect.

Alternative: DATEDIF for Day Count

Instead of subtracting dates directly, use DATEDIF: =(B2/30)*(DATEDIF(C2,D2,"D")+1). Same result, different syntax.

Note that the formula uses D2-C2+1 for day count. If the dates were real Excel date values instead of text, the formula would be simpler: =(B2/30)*(D2-C2+1). The DATE conversion in the solution handles the text-to-date parsing so the math works correctly.

Going Further: Business Day Prorating

If billing counts only working days: =(B2/20)*NETWORKDAYS(C2,D2). Uses 20 business days instead of 30 calendar days.

The +1 in the formula satisfies the inclusive date rule. Without it, a service period that starts and ends on the same day would return 0 days instead of 1. That small adjustment prevents underbilling by one day on every short-term subscription.

The 30-day assumption is a simplification. Some businesses use 365 days or actual month days. The formula structure stays the same — only the denominator changes.