Revenue structure

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:

1. 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.
2. 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.
3. 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.