The Problem
Billing gets messy when a customer uses a service for only part of a month. Charging the full monthly fee can feel unfair, but guessing a smaller amount by hand creates a different problem: two similar customers might end up with different rules.
A good prorated sheet solves that by turning the billing rule into a repeatable process. The monthly fee needs to become a daily rate, the service dates need to become a count of active days, and the final charge should come from those two pieces working together.
The flow below keeps those moving parts separate before they meet in the final bill. That makes the problem easier to reason about than trying to jump straight from dates and fees to a final amount.
The Problem: Fair Partial-Month Billing A monthly fee needs to be adjusted by the exact service period before it becomes a customer charge.
In this workbook, each service has a monthly fee, a start date, and an end date. The sheet uses a simple 30-day month rule so the user can focus on the core billing logic: count the active period consistently, calculate the partial charge, and then confirm the customer count and total revenue in the audit section.
- The billing rule is based on a 30-day month.
- The service period includes both the start date and the end date.
- The audit section checks the number of customer rows and the total amount to collect.
Those details matter because prorating is less about fancy math and more about consistent assumptions. If the day count rule changes from row to row, the final bills stop being trustworthy.
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 because it mirrors the billing story: one monthly fee becomes a daily rate, then the daily rate is applied only to the days the service was active.
It works well when the business already uses a fixed month length, like the 30-day rule in this challenge. Because the sample dates are stored as text, the formula first converts each date part into a real Excel date before subtracting them. The key detail is the inclusive day count, because a same-day service period should still bill for one day, not zero.
=(B2/30)*(DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2))-DATE(LEFT(C2,4),MID(C2,6,2),RIGHT(C2,2))+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 make the day-count part easier to recognize. Instead of relying on direct date subtraction, the formula says clearly that it wants the difference between two dates in days.
This method is helpful in workbooks where the billing logic needs to be reviewed by someone else. You still need to handle the inclusive rule, and in this workbook the text dates still need to be converted before DATEDIF can compare them safely.
=(B2/30)*(DATEDIF(DATE(LEFT(C2,4),MID(C2,6,2),RIGHT(C2,2)),DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2)),"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 because weekends should not be charged as active service days.
That is not the rule used in this challenge, but it is a useful contrast. The same start and end dates can produce different bills depending on whether the business counts calendar days or workdays.
=(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, that date difference is what turns the service period into something the billing formula can use.
The important detail is that date math only works after Excel sees the values as real dates. Once that is true, the +1 handles the inclusive billing rule so a one-day service does not incorrectly look like zero days.
2. DATEDIF
DATEDIF returns a date difference in a chosen unit. Here, the useful unit is days because the final bill depends on how long the service was active.
It is another valid way to calculate the used period before applying the daily rate, especially when you want the formula to make the date-difference step obvious.
3. SUM and COUNTA
SUM totals the billed amounts, and COUNTA counts how many service rows were processed. Together, they check whether the billing list looks complete.
Those two summary functions turn the row calculations into a quick billing overview, which is useful before sending invoices or reviewing expected revenue.
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.