Phasing Costs based on a start and End date

KateNash

Board Regular
Joined
Sep 23, 2004
Messages
86
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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:
Code:
=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

Board Regular
Joined
Sep 23, 2004
Messages
86

ADVERTISEMENT

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

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
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.
 
Last edited:

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277

ADVERTISEMENT

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
Code:
 would prevent formula changes.


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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
Code:
 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.
 

KennyBoy

New Member
Joined
Feb 4, 2019
Messages
1
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
Code:
 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top