# Multiple Budgets over a range of months and years, with monthly cost/estimate showing by month for each project

#### IwannaBeLikeU

##### New Member
I have hundreds of rows of data. Each row is a project and cost with start date and end date. Some projects are old and complete, others have started and are ongoing and some have not even started. Using the full range of dates, I want to show the cost of each project by month. I've added the number of days into the data and calculated the cost by day. I figure I need a rule that says 3 things. if the start month equals the reference month, then calculate the days remaining* daily cost, or if end month equals reference month calculate days upto end date*daily cost. Then a separate argument that says if the reference month is in between the startdate and enddate, then calculate the no. days * daily cost.

I've been racking my brains about this for days. I can make one function work but not the other and so now I am at a total loss.

I've attached a simple version of my spread sheet to help with visualization.

 Project Start Date End Date Budget Days Cost Per Day 05/01/15 06/01/15 Etc 6/1/2017 Project A 5/17/15 8/3/15 10000 78 128.21 Project B 10/12/15 1/25/17 50000 471 106.16 Project C 2/15/17 3/31/17 45000 44 1022.73

<tbody>
</tbody>

All help would be greatly appreciated :D

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### RCBricker

##### Well-known Member
so basically you what project A for 05/01/15 to calculate the cost in days from 05/17/2015 - 05/31/2015. the same would hold true for August of 2015 you want the cost in days for 08/01/2015 - 08/03/2015. And finally you want June and July to show the cost in days for each of those months 1 - 30(1) respectively. Correct?

#### IwannaBeLikeU

##### New Member
Yes, that is correct.

#### RCBricker

##### Well-known Member
Yes, that is correct.

try this

Code:
``````=IF(AND(YEAR(\$B2)=YEAR(G\$1),MONTH(\$B2)=MONTH(G\$1)),SUM(DAY(EOMONTH(\$B2,0))-DAY(\$B2))*\$F2,
IF(AND(MONTH(G\$1)<MONTH(\$C2),MONTH(G\$1)>MONTH(\$B2)),SUM(DAY(EOMONTH(G\$1,0))*\$F2),IF(AND(YEAR(\$C2)=YEAR(G\$1),MONTH(\$C2)=MONTH(G\$1)),DAY(\$C2)*\$F2,"")))``````

#### RCBricker

##### Well-known Member
I have hundreds of rows of data. Each row is a project and cost with start date and end date. Some projects are old and complete, others have started and are ongoing and some have not even started. Using the full range of dates, I want to show the cost of each project by month. I've added the number of days into the data and calculated the cost by day. I figure I need a rule that says 3 things. if the start month equals the reference month, then calculate the days remaining* daily cost, or if end month equals reference month calculate days upto end date*daily cost. Then a separate argument that says if the reference month is in between the startdate and enddate, then calculate the no. days * daily cost.

I've been racking my brains about this for days. I can make one function work but not the other and so now I am at a total loss.

I've attached a simple version of my spread sheet to help with visualization.

 Project Start Date End Date Budget Days Cost Per Day 05/01/15 06/01/15 Etc 6/1/2017 Project A 5/17/15 8/3/15 10000 78 128.21 Project B 10/12/15 1/25/17 50000 471 106.16 Project C 2/15/17 3/31/17 45000 44 1022.73

<tbody>
</tbody>

All help would be greatly appreciated :D

Found an error in my formula

Code:
``=IF(AND(MONTH(G\$1)=MONTH(\$B2),YEAR(G\$1)=YEAR(\$B2)),SUM(DAY(EOMONTH(G\$1,0))-DAY(\$B2))*\$F\$2,IF(AND(MONTH(G\$1)=MONTH(\$C2),YEAR(G\$1)=YEAR(\$C2)),SUM(DAY(\$C2)*\$F\$2),IF(OR(G\$1<\$B2,G\$1>\$C2),"",SUM(DAY(EOMONTH(G\$1,0))*\$F\$2))))``

Replies
1
Views
170
Replies
4
Views
302
Replies
3
Views
295
Replies
2
Views
509
Replies
1
Views
154

1,190,743
Messages
5,982,703
Members
439,790
Latest member
jonaust

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