# 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

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