WPS Spreadsheet: Calculate Contract Revenue by Month Between Start and End Dates
Question details
Calculate the portion of each contract's value that belongs to every month between its start and end dates.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains a contract value, start date, and end date, and needs to distribute revenue across months based on the actual number of contract days in each month.
- Observed behavior
- The monthly allocation is unclear for contracts that begin or end mid-month, such as a contract running from October 15 through June 30.
Confirm that the contract value, start date, and end date are stored as numbers recognized as dates in WPS Spreadsheet. Set each monthly header to the first day of its month, such as 10/1/2023, and format the header as Month Year if desired.
Calculate revenue from actual overlapping days in each month
Use this method when revenue must be allocated according to the exact number of contract days in each calendar month.
Assume the contract value is in B2, the start date is in C2, the end date is in D2, and the monthly header in F1 is the first day of the target month. The formula counts the inclusive overlap between the contract period and that calendar month, then multiplies it by the contract's daily value.
In F1, enter the first day of the target month, such as 10/1/2023. Enter later month starts in G1, H1, and the columns that follow.
Select F2 and enter =MAX(0,MIN($D2,EOMONTH(F$1,0))-MAX($C2,F$1)+1)*$B2/($D2-$C2+1). Press Enter to calculate the revenue for that month.
Drag the fill handle from F2 across the remaining month columns. The relative F$1 reference changes to each month while the contract cells remain fixed to row 2.
Select the calculated cells, choose Home > Number Format > Currency, and verify that months before the start date or after the end date show 0.
Allocate the contract value evenly across active calendar months
Use this alternative only when the contract terms specify equal revenue for every calendar month touched by the contract.
Build date-based contract revenue schedules in WPS Spreadsheet
WPS Spreadsheet can calculate daily contract revenue for each calendar month with MAX, MIN, and EOMONTH, then fill the formula across a complete revenue schedule. It also supports an equal-month alternative when that matches the contract terms.
- 1. Create the schedule: Open WPS Spreadsheet and place each contract's value, start date, and end date in separate columns, with monthly start dates across the top row.
- 2. Enter the daily-proration formula: Select the first monthly result cell and enter =MAX(0,MIN($D2,EOMONTH(F$1,0))-MAX($C2,F$1)+1)*$B2/($D2-$C2+1), adjusting the cell references to match your columns.
- 3. Fill the schedule: Use the fill handle to copy the formula across all months and down all contract rows.
- 4. Review totals and formatting: Apply Currency formatting from Home > Number Format, then use AutoSum to compare each contract's monthly total with its original value.
Frequently Asked Questions
How does the WPS Spreadsheet formula calculate the number of days in a month?
It uses MAX to select the later of the contract start date and the month start, and MIN to select the earlier of the contract end date and the month end from EOMONTH. The +1 makes the date range inclusive.
What happens when a contract does not overlap a monthly column?
MAX(0,...) changes a negative overlap into 0, so months before the contract starts or after it ends receive no revenue.
Should I divide the contract value by nine for a contract from October 15 through June 30?
Only if the contract requires equal allocation across the nine calendar months. For actual-day allocation, divide the value by the total contract days and multiply by the overlapping days in each month.
Why does the monthly header need to be the first day of the month?
The formula uses the header as the month start and EOMONTH(header,0) as the month end. Entering the first day consistently makes the formula easy to copy across columns.
Can I use this calculation for multiple contracts?
Yes. Keep the contract references in the formula fixed by column, such as $B2, $C2, and $D2, then drag the formula down. The row number changes automatically for each contract.
