# Phasing Costs based on a start and End date

#### KateNash

##### Board Regular
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?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### RonB1111

##### Well-known Member
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
Amazing - thank you! I would never have got there!

#### RonB1111

##### Well-known Member
You're welcome. Thank you for providing feedback.

#### KateNash

##### Board Regular

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
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

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
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.``````

#### KateNash

##### Board Regular
Works like a charm now! Thanks!!!

#### KennyBoy

##### New Member
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?``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,670
Messages
5,838,688
Members
430,563
Latest member
Raeyven

### 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.

### Which adblocker are you using?

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

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