Forum Discussion
Revenue structure
HelloDharmendra_Bharwad,
You can solve both termination and fee update logic with one formula that works in Excel and Google Sheets.
Assumptions
Row 2 contains real month-end dates (Jan 2026 to Dec 2027)
Column A = Client Name
Columns B onward = Monthly revenue
AA = Start Date
AB = Termination Date (blank if active)
AC = Base Fee
AD = Fee Change Date
AE = New Fee
Formula to enter in B3 and copy across and down:
=IF(OR(B$2<$AA3,AND($AB3<>"",B$2>=$AB3)),0,IF(AND($AD3<>"",B$2>=$AD3),$AE3,$AC3))
Logic explanation
Start control
If the month is before the Start Date, revenue is zero.
Termination logic
If a Termination Date exists and the month is on or after that date, revenue becomes zero.
All future months automatically remain zero. No manual clearing required.
Fee update logic
If the month is on or after the Fee Change Date, the New Fee applies.
Earlier months remain at the Base Fee.
The updated fee flows automatically into the next year unless changed again.
Total column formula
=SUM(B3:Y3)
This structure automatically updates totals, requires no manual zeroing, and works in both Excel and Google Sheets.
Hi Olufemi7 , Thanks for your response. I tried the formula at my end but it's not working. Can you please stress test at your end? I have attached the screenshot for your easy reference.
I have tested this with your structure where month-end dates are in Row 2 and revenue runs across with Total at the end. It handles mid-month increment dates, mid-month termination dates, blank termination dates, blank increment dates, automatic zeroing of future months, correct annual totals, and works in both Excel and Google Sheets. Use this formula in the first revenue cell for example G4 and copy across and down: =IF(EOMONTH(G$2,0)<EOMONTH($B4,0),0,IF(AND($F4<>"",EOMONTH(G$2,0)>=EOMONTH($F4,0)),0,IF(AND($D4<>"",EOMONTH(G$2,0)>=EOMONTH($D4,0)),$E4,$C4))). Start logic: Revenue is 0 before the start month. Termination logic: If a termination date exists that month becomes 0 and all future months remain 0 automatically. If termination is blank the base fee continues. Fee update logic: If an increment date exists the new fee applies from that month forward. Prior months remain unchanged. If increment is blank the base fee continues normally. Total column example if months run from G to AE: =SUM(G4:AE4). I tested all scenarios including termination only, increment only, both, or neither, and all work as required.
