Phasing Costs based on a start and End date

[KateNash]

Hi

I have a spreadsheet which is an export from a project management software (Primavera)

It has start date, end date and budget.

I need to somehow produce an output in Excel which shows a phased budget.

For example
Start Date End Date Budget
15/01/2013 31/03/2013 15,000

I would want somehow to produce an output which applies 15,000 split into January, February and March, but pro-rata for the days required in each month: e.g. there are 75 days in the period so 15,000 / 75 = 200 per day, then:

Jan-13 = 3,200 (16 days at 200 per day)
Feb-13 = 5,600 (28 days at 200 per day)
Mar-13 = 6,200 (31 days at 200 per day)

Is this possible?

 

[RonB1111]

Headers in row 1, data begins row 2.
In D1, enter 1-Jan-13, in E1, 1-Feb-13, etc
Enter this formula in D2 and copy across and down:

=IF(OR($A2>EOMONTH(D$1,0),$B2<D$1),0,IF(AND($A2>=D$1,$A2<=EOMONTH(D$1,0)),MIN(EOMONTH(D$1,0)-$A2+1,$B2-$A2+1)/($B2-$A2+1)*$C2,MIN(EOMONTH(D$1,0)-D$1+1,$B2-D$1+1)/($B2-$A2+1)*$C2))

Also, in your example, a start date of 15-Jan would result in 17 days in Jan and 76 total days.

 

[KateNash]

Amazing - thank you! I would never have got there!

 

[RonB1111]

You're welcome. Thank you for providing feedback.

 

[KateNash]

When I try it, I get an error and suggested new formula but it doesn't work....it is suggesting I use this but if I click No it says there is an error in the formula:

=IF(OR($A3>EOMONTH(D$1,0),$B3=D$1,$A3<=EOMONTH(D$1,0)),MIN(EOMONTH(D$1,0)-$A3+1,$B3-$A3+1)/($B3-$A3+1)*$C3,MIN(EOMONTH(D$1,0)-D$1+1,$B3-D$1+1)/($B3-$A3+1)*$C3)

 

[RonB1111]

Sorry about that. The formula I originally posted did not come thru correctly. I'm having difficulty getting it to post correctly without dropping part of the formula. I'll try again in a minute with another approach.

 

[RonB1111]

OK, the correct formula is posted below with spaces before and after every < or > sign (it wraps over to a 2nd line). You can either manually remove the spaces, or paste the formula in D2 and excel will tell you there's an error and offer a corrected formula with the spaces removed.

=IF(OR($A2 > EOMONTH(D$1,0),$B2 < D$1),0,IF(AND($A2 < =EOMONTH(D$1,0),$A2 > =D$1),MIN(EOMONTH(D$1,0)-$A2+1,$B2-$A2+1)/($B2-$A2+1)*$C2,MIN(EOMONTH(D$1,0)-D$1+1,$B2-D$1+1)/($B2-$A2+1)*$C2))

edit: PS KateNash - I verified after posting the above that it's the correct formula this time.

Question for any moderator or MVP:
I wrapped the orignal formula in the CODE signs and it dropped/changed part of the formula - this repeated in my attempted repost of the formula, so I resorted to putting spaces around < and > signs. Could someone tell me why? - I thought wrapping a formula with

 would prevent formula changes.


[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Smitty]

Ron,

Question for any moderator or MVP:
I wrapped the orignal formula in the CODE signs and it dropped/changed part of the formula - this repeated in my attempted repost of the formula, so I resorted to putting spaces around < and > signs. Could someone tell me why? - I thought wrapping a formula with

 would prevent formula changes.[/quote]

It has to do with the way that the board views/tries to render the < > signs.  It's pretty much limited to just formulas with those operators in them.

 

[KateNash]

Works like a charm now! Thanks!!!

 

[KennyBoy]

OK, the correct formula is posted below with spaces before and after every < or > sign (it wraps over to a 2nd line). You can either manually remove the spaces, or paste the formula in D2 and excel will tell you there's an error and offer a corrected formula with the spaces removed.

=IF(OR($A2 > EOMONTH(D$1,0),$B2 < D$1),0,IF(AND($A2 < =EOMONTH(D$1,0),$A2 > =D$1),MIN(EOMONTH(D$1,0)-$A2+1,$B2-$A2+1)/($B2-$A2+1)*$C2,MIN(EOMONTH(D$1,0)-D$1+1,$B2-D$1+1)/($B2-$A2+1)*$C2))

edit: PS KateNash - I verified after posting the above that it's the correct formula this time.

Question for any moderator or MVP:
I wrapped the orignal formula in the CODE signs and it dropped/changed part of the formula - this repeated in my attempted repost of the formula, so I resorted to putting spaces around < and > signs. Could someone tell me why? - I thought wrapping a formula with

 would prevent formula changes.


[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]This is a very clever piece of formulae, particularly for the accountants among us. I wonder if a similar function can be performed which phases the costs in the year by week number?