Amount spread between two dates, use start date unless passed use alternate date

[BeadyBob]

Hello,
I would like to ask for help with a revenue forecasting spread formula currently in use.

Some Background. When a forecasted month passes, the actual amounts are entered over the forecast formula in that month column.
The issue I need to resolve is that the remaining months for forecast do not forecast out the full balance amount since the formula still spreads the allocation from start to end date.
My thought is to add an override start date that represents the current start date for forecasting. For example, the start date was 3/10/24 but we are now forecasting for April - Dec. I would want the formula to look at cell A2 if the date in cell G5 has passed. How would I add that into the existing formula, to use A2 if G5 has passed? After several unsuccessful attempts, I am at a loss.

=IFERROR(MAX(0,MIN($H5+1,EDATE(O$4,1))-MAX($G5,O$4))/($H5-$G5+1)*$E5,0)
G5 = Start Date
H5 = End Date
E5 = Amount Balance
O4 = Month column
A2 would contain a current forecast start intended to be the override for P5 if that date has passed.

 

[myall_blues]

I would probably do it this way:

In another cell (say A3), put the formula:

=IF(G5<=TODAY(),G5,A2)

The contents of A3 will then be equal to G5 if G5 is less than or equal to today's date, otherwise it will equal A2.

Then change your formula to refer to A3 instead of G5.

=IFERROR(MAX(0,MIN($H5+1,EDATE(O$4,1))-MAX($A$3,O$4))/($H5-$A$3+1)*$E5,0)

If you need multiple lines add a column to contain the A3 formula instead of referring to just a single cell.

 

[BeadyBob]

Thank you very much myall_blues, I appreciate your response. This will help me in a pinch to make sure the full balance amount is getting forecasted.
Some additional info., each forecast spreadsheet can have hundreds of lines. The "start date" in G5 isn't always in the past. If I use this formula for all lines it overrides start dates that shouldn't be overridden.
Is there a way to adjust the formula to only use A2 if G5 is in the past?

 

[myall_blues]

What I meant by this line in my response:

If you need multiple lines add a column to contain the A3 formula instead of referring to just a single cell.

is to put the formula in a column instead of just A3.
So for example let’s say you decide to use column Q.
Then in Q5 put the formula

=IF(G5<=TODAY(),G5,$A$2)

and copy it down so that in Q6 it becomes

=IF(G6<=TODAY(),G6,$A$2)

and so on down the column.
Then your other formula will refer to the relevant cell in column Q

=IFERROR(MAX(0,MIN($H5+1,EDATE(O$4,1))-MAX(Q5,O$4))/($H5-Q5+1)*$E5,0)

Does that help?

 

[BeadyBob]

Oh, I see! Thank you for explaining further, I misunderstood initially. I used your logic and tested as a start date override on each line, it works perfectly. My issue is resolved, thank you so much!

 

[myall_blues]

Excellent. I’m glad we got it sorted.