Forum Discussion
Revenue structure
The challenge is to make these formulas dynamic so that when a termination date or fee change is entered, all future months automatically update without manual intervention. Also, the solution should work in both Excel and Google Sheets.
I think using a combination of IF statements and date comparisons would work. For termination, the formula in each month's revenue cell would check if the termination date is before or equal to the month in the column header. If yes, return zero; else, use the previous month's revenue (or the original value if it's the first month).
I think the solution involves setting up a table with client names, termination dates, fee change dates, and then using formulas that reference these dates to determine the revenue for each month.
To implement the termination and fee update logics in Excel/Google Sheets with automatic future-month adjustments, you can follow this structured approach:
Formula for Monthly Revenue (e.g., Cell B3 for Client A, Jan 2026):
=IF(B$2 >= $C3, 0,
IF(ROW(B3)=ROW($B$3), $D3,
IF(B2=0, 0, B2)))
Annual Total (e.g., Cell N3):
=SUM(B3:M3)
Fee Update Logic Implementation
Setup Fee Schedule Table (e.g., Columns O:R):
Client | Fee Change Date | New Fee | Exact Match Flag |
A | 2026-07-01 | 1500 | TRUE |
Formula for Monthly Fee (e.g., Cell D3 for Client A):
=IFERROR(
XLOOKUP(B$2, $O:$O, $R:$R, $D$2, 0, 1),
$D$2)
Replace XLOOKUP if in Google Sheets is unavailable.
=IFERROR(VLOOKUP(B$2, $O:$R, 4, 1), $D$2)
Date Formatting: Ensure termination dates (Column C) and fee change dates (Column O) are stored as YYYY-MM-DD (e.g., 2026-07-01) for reliable comparisons.
Fee Schedule Sorting: If using VLOOKUP, sort the fee schedule table by date in ascending order. XLOOKUP doesn’t require sorting.
Total Column: Use =SUM(B3:M3)—it will dynamically exclude zeroed months post-termination.
I Think this approach is scalable, maintainable, and error-resistant. Test it with a small dataset first to confirm behavior, then scale to your full dataset. For Google Sheets, prioritize XLOOKUP or exact-match VLOOKUP to avoid lookup errors.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Hi NikolinoDE , The formula is not working. Can you please check it at your end? Below is the screenshot of excel version.
Based on the screenshot you provided, I can see the exact column layout. The previous formula I gave might have been based on a different column assumption.
Here is the corrected, robust solution tailored specifically to the column layout in your screenshot (Columns B through F).
Place this formula in cell G3 (which corresponds to Jan 26 for Client A) and drag it right and down.
=IF(G$2 < $B3, 0, IF(G$2 >= $F3, 0, IF(G$2 >= $D3, $E3, $C3)))
*If your Excel uses semicolons instead of commas, use: =IF(G$2 < $B3; 0; IF(G$2 >= $F3; 0; IF(G$2 >= $D3; $E3; $C3)))).
If you says "It's not working," , please check these 3 things:
- Date Formats: Ensure Row 2 (Jan 26, Feb 26) are actual Dates and not just text. If they are text, the comparison G$2 >= $B3 will fail.
- Absolute References: Ensure they copied the formula exactly, especially the $ signs. If they drag the formula and the references shift (e.g., B3 becomes C3), the logic breaks.
- Termination Logic: The formula assumes that if the Termination Date is, for example, 14/11/2026, the revenue for November is 0. If they want revenue for the month of termination, change G$2 >= $F3 to G$2 > $F3. However, standard accounting usually cuts off revenue on the termination date, so >= is safer.
"I have adjusted the formula based on the screenshot you sent. The columns are slightly different than the generic description. Please use this specific formula in cell G3 (Jan 26 for Client A) and drag it across and down:=IF(G$2 < $B3, 0, IF(G$2 >= $F3, 0, IF(G$2 >= $D3, $E3, $C3)))
This handles the automatic zeroing of future months and the fee escalation without needing to change the formula for next year.
My answers are voluntary and without guarantee!
Hope this will help you.
