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

[ahoward303]

Start Date	End Date	Project Value	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
Jun-18	Dec-18	$42,000	$6,000	$6,000	$6,000	$6,000	$6,000	$6,000	$6,000

<tbody>
</tbody>

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!