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

IwannaBeLikeU

New Member
Joined
Nov 29, 2016
Messages
2
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.

ProjectStart DateEnd DateBudgetDaysCost Per Day05/01/1506/01/15Etc6/1/2017
Project A5/17/158/3/151000078128.21
Project B10/12/151/25/1750000471106.16
Project C2/15/173/31/1745000441022.73

<tbody>
</tbody>


All help would be greatly appreciated :D
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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?
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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
Joined
Feb 4, 2003
Messages
1,560
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.

ProjectStart DateEnd DateBudgetDaysCost Per Day05/01/1506/01/15Etc6/1/2017
Project A5/17/158/3/151000078128.21
Project B10/12/151/25/1750000471106.16
Project C2/15/173/31/1745000441022.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))))
 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top