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

[IwannaBeLikeU]

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]

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]

Yes, that is correct.

 

[RCBricker]

Yes, that is correct.

try this

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

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

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