Probably not as simple as I'm hoping....

[ahoward303]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Project Value[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[/TR]
[TR]
[TD]Jun-18[/TD]
[TD]Dec-18[/TD]
[TD]$42,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[TD]$6,000[/TD]
[/TR]
</tbody>[/TABLE]

I have a spreadsheet that lists end dates & start dates, project values & the months of Jun 2018 - Oct 2019. What I'm wanting to do is forecast the project value based on the start and end dates. For example. I have a $42,000 project that will start in June 2018 and complete in Dec 2018. I'm wanting a formula that will forecast it for me so anytime a start or end date is changed the forecast for that project will change. Is that possible or am I expecting too much?

Thank you for any help you can provide!

Ashley

 

[jproffer]

So basically, in your example, you want to divide 42,000 by 7 months, but if you change Dec-18 to Oct-18, you then want to divide by 5?

To subtract the months, use this:

=MONTH(B2-A2)

or for the whole formula at once, try this:

=C2/(MONTH(B2-A2))

 

[Marcelo Branco]

See if this helps

https://www.mrexcel.com/forum/excel...schedule-formula-post5084177.html#post5084177

M.

 

[Tetra201]

Try placing the following formula in cell D2 and drag-copying it to the right as needed:

=IF(AND($A2<=D$1,D$1<=$B2),$C2/(MONTH($B2)-MONTH($A2)+1),"")

 

[ahoward303]

Try placing the following formula in cell D2 and drag-copying it to the right as needed:

=IF(AND($A2<=D$1,D$1<=$B2),$C2/(MONTH($B2)-MONTH($A2)+1),"")

This one is working beautifully! Thank you so much!! However, when I try to span more than one year, like from June 2018 to July 2019, the dollar amount is quadrupled.

 

[ahoward303]

See if this helps

https://www.mrexcel.com/forum/excel...schedule-formula-post5084177.html#post5084177

M.

Thank you!! I wasn't looking at calculating by number of days in the month but that's actually not a bad idea!!!

 

[ahoward303]

this one didn't work for me. It calculates the same amount for each month even if its not in the timeframe I'm needing.

 

[Tetra201]

This one is working beautifully! Thank you so much!! However, when I try to span more than one year, like from June 2018 to July 2019, the dollar amount is quadrupled.

Acknowledged. Please try the following updated formula:

=IF(AND($A2<=D$1,D$1<=$B2),$C2/DATEDIF(EOMONTH($A2,-1)+1,EOMONTH($B2,0)+1,"m"),"")

 

[ahoward303]

Acknowledged. Please try the following updated formula:

=IF(AND($A2<=D$1,D$1<=$B2),$C2/DATEDIF(EOMONTH($A2,-1)+1,EOMONTH($B2,0)+1,"m"),"")

That did it!! Thanks so much for your help!