The Problem
In this guide, we will explore exactly how to calculate pro-rated costs in Excel. This comes up when a customer uses a service for only part of the month and should not be billed for the full monthly amount.
The hard part is consistency. If you count the used days manually, it is easy to overcharge or undercharge, especially when start and end dates vary from row to row.
In this challenge, each service has a monthly fee plus a start and end date. You need to calculate the prorated bill for each row based on a 30-day month, then finish the summary section with the customer count and the total amount to collect.
How We Solve It
The basic pattern is to divide the monthly fee by 30 to get a daily rate, then multiply that rate by the number of used days.
The challenge treats the start and end dates as inclusive, which is why the formula adds 1 day after subtracting the dates. That small detail matters because it changes the billed amount.
Method 1: Daily Rate Times Days Used
Method 1: Turn the monthly fee into a daily rate and multiply it by days used.
This is the clearest version of the prorating formula. First calculate the daily rate, then multiply it by the inclusive day count.
It works well when the month length is fixed by business rule, like the 30-day assumption used in this challenge.
=(B2/30)*(D2-C2+1)
Method 2: Use DATEDIF for the Day Count
Method 2: Use DATEDIF when you want Excel to return the day count explicitly.
DATEDIF can return the difference between the start and end date in days. Since the challenge counts both endpoints, you still add 1 after the function result.
This is useful when you want the day-count step to be easy to recognize inside the formula.
=(B2/30)*(DATEDIF(C2,D2,"d")+1)
Method 3: Workday Billing Variant
Method 3: Use NETWORKDAYS only when the billing model is based on workdays instead of calendar days.
Some billing models count only working days. In that case, NETWORKDAYS can replace simple subtraction.
That is not the billing rule used in this challenge, but it is a helpful contrast because it shows how the same setup can support a different business rule.
=(B2/20)*NETWORKDAYS(C2,D2)
Function Explanation
1. Date Subtraction
Subtracting one date from another returns the number of days between them. In this challenge, it is part of the core billing formula.
The important detail is the +1 because the billing period includes both the start date and the end date.
2. DATEDIF
DATEDIF returns a date difference in a chosen unit. Here, the useful unit is days.
It is another valid way to calculate the used period before applying the daily rate.
3. SUM and COUNTA
SUM totals the billed amounts, and COUNTA counts how many service rows were processed.
Those two summary functions turn the row calculations into a quick billing overview.
Learn more this functionSUM
In a real billing model, month length might not always be fixed at 30. This challenge keeps it simple, but that assumption is something you would always confirm in a live workbook.
Calculate the prorated bill for each subscription based on how many days it was active during the month. Use the 30-day rule shown in the sheet, then complete the billing summary so the worksheet shows how many customers were processed and the full amount that should be collected.