Help with calculations based on dates.

[Alex Barrosse]

So I'm working on a project and I need to calculate a total year cost (from today's date forward) based on a fixed monthly cost for each full month, and then the percentage of the monthly cost based on how many days into a month a project is ending.

<TABLE style="WIDTH: 473pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=629 border=0 x:str><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 39pt" height=52><TD class=xl830 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 102pt; BORDER-BOTTOM: gray 1pt solid; HEIGHT: 39pt; BACKGROUND-COLOR: transparent" width=136 height=52>Start Commitment Date</TD><TD class=xl830 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 86pt; BORDER-BOTTOM: gray 1pt solid; BACKGROUND-COLOR: transparent" width=114>End Commitment Date</TD><TD class=xl827 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 131pt; BORDER-BOTTOM: gray 1pt solid; BACKGROUND-COLOR: transparent" width=174 x:str="Monthly Cost USD">Monthly Cost USD</TD><TD class=xl831 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 154pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=205 x:str="Committed Spend Jul-Dec 11">Committed Spend Jul-Dec 11 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl832 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40330">06/01/10</TD><TD class=xl832 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="40754">07/30/11</TD><TD class=xl828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="34365.81">30,000 </TD><TD class=xl829 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl832 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40395">08/05/10</TD><TD class=xl832 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="40759">08/04/11</TD><TD class=xl828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="16712.45">15,000 </TD><TD class=xl829 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl833 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="40594">2/20/2011</TD><TD class=xl833 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="40774">8/19/2011</TD><TD class=xl828 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="36626.9">40,000 </TD><TD class=xl829 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver"></TD></TR></TBODY></TABLE>

basically I need a formula to sum all of the full months and the partial month.
Ex. for the 2nd row, the commited spend would be
15,000 (for july) + 0.129 * 15,000 (prorated cost for the 4 days in august).

The start date can be anything as early as 2 years prior to now but I only need data from July 1st, 2011 forward and the end date can be as far as 2 years in advance but I only need up to Dec 31, 2011.

I appreciate any ideas. Thanks.

 

[Ralajer]

This is not exactly what you need but I am not sure I understand fully your requirements. It has the basic components for determining full and partial month you will have to apply the bounding for Jul-Dec.

Number of months not counting the first and last months. (Negative 1 if start and end are the same month & year)

=MONTH(B2)+12*YEAR(B2)-MONTH(A2)-12*YEAR(A2)-1

Percentage of days for start month

=1-(DAY(A2)-1)/DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)

Percentage of days for end month

=DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,1)-1)

Combined

=(MONTH(B2)+12*YEAR(B2)-MONTH(A2)-12*YEAR(A2)-1)+(1-(DAY(A2)-1)/DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1))+(DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,1)-1))*C2

Where
• A2 = Start Date
• B2 = End Date
• C2 = Montly Cost

 

[shawnhet]

Hi

When I copy the above over, I don't get the right answer - here is my version A2 date for start of report, B2 end of project, C2 monthly cost

=(YEAR(B2)*12-YEAR(A2)*12+MONTH(B2)-MONTH(A2)+DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,1)-1)-(DAY(A2)-1)/DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1))*C2

This seems to work for me.

Cheers,

 

[markmzz]

Or try this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; COLOR: #000080; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Start Commitment Date</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; COLOR: #000080; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">End Commitment Date</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; COLOR: #000080; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Monthly Cost USD</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Committed Spend Jul-Dec 11 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6/1/2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7/30/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">30,000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">29,032.26</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8/5/2010</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8/4/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">15,000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16,935.48</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2/20/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8/19/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40,000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">64,516.13</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8/10/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8/19/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40,000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24,516.13</TD></TR></TBODY></TABLE>Sheet1


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=(DATEDIF(MAX(DATE(2011,7,1),A2),B2+1,"m")+(DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0))))*C2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[Alex Barrosse]

Thanks for all the suggestions everyone.

Markmzz, yours seemed to work best for me, but I ended up making one minor change, changing

=(DATEDIF(MAX(DATE(2011,7,1),A2),B2+1,"m")+(DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0))))*C2
to

=(DATEDIF(MAX(DATE(2011,7,1),A2),B2-1,"m")+(DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0))))*C2

which handled an incorrect value for any end date that was the last day of a month.

I had one other constraint that was added and I was wondering if you might know how to handle it. For end dates that are the first day of a new month (i.e. 11/1/2011) I don't need to include that one day, just up through the last day of the previous month.

Would an IF statement work, to test if the day in the end commitment date is the 1st, and if so, just have the committed spend be the monthly cost * the number of full months prior to the 1st? Thanks again for any ideas you might have.

 

[markmzz]

Alex Barrosse,

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #EFEFEF;;">Start Commitment Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #EFEFEF;;">End Commitment Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #EFEFEF;;">Monthly Cost USD</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Committed Spend Jul-Dec 11 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #EFEFEF;;">Months</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #000080;background-color: #EFEFEF;;">Days</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">7/30/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">28,064.52</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">28,064.52</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">7/31/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">29,032.26</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">29,032.26</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/1/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/5/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/4/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">15,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">16,451.61</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">15,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">1,451.61</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">2/20/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/19/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">40,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">63,225.81</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">40,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">23,225.81</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">2/20/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/19/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">40,001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">63,227.39</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">40,001.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">23,226.39</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">8/10/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">11/29/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">40,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">157,333.33</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">120,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">37,333.33</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Plan3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=(<font color="Blue">DATEDIF(<font color="Red">MAX(<font color="Green">DATE(<font color="Purple">2011,7,1</font>),A2</font>),B2,"m"</font>)+MOD(<font color="Red">(<font color="Green">DAY(<font color="Purple">B2</font>)-1</font>)/DAY(<font color="Green">DATE(<font color="Purple">YEAR(<font color="Teal">B2</font>),MONTH(<font color="Teal">B2</font>)+1,0</font>)</font>),1</font>)</font>)*C2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=DATEDIF(<font color="Blue">MAX(<font color="Red">DATE(<font color="Green">2011,7,1</font>),A2</font>),B2,"m"</font>)*C2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">B2</font>)=1,0,MOD(<font color="Red">(<font color="Green">DAY(<font color="Purple">B2</font>)-1</font>)/DAY(<font color="Green">DATE(<font color="Purple">YEAR(<font color="Teal">B2</font>),MONTH(<font color="Teal">B2</font>)+1,0</font>)</font>),1</font>)</font>)*C2</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Alex Barrosse]

Thanks for the updated formulas.

A couple things:

As with before, the values are all just a little off unless I change

=(DATEDIF(MAX(DATE(2011,7,1),A2),B2,"m")+MOD((DAY(B2)-1)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0)),1))*C2

to

=(DATEDIF(MAX(DATE(2011,7,1),A2),B2,"m")+MOD((DAY(B2))/DAY(DATE(YEAR(B2),MONTH(B2)+1,0)),1))*C2

That gives me the right values for all days of the month that aren't the first or last.

I still need the committed spend for dates ending on the last day of a month to include that full month. It looks like your formula doesn't include the last day, for example in row 3 of your post, the committed spend should be 30,000 for both 7/31/2011 and 8/1/2011.

Also, curiously, I was just fooling around with the formula more, and, like I said when I remove the "-1" as I described above, the formula works for all the values that aren't the first or last day of the month but when I leave it in, all the values are off by one day's worth of cost, EXCEPT the first day of each month, which correctly returns the number of months prior to that day * monthly cost.

Any ideas? And thanks for your help, this has been stumping me for a while.

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #FFFFFF;;">Start Commitment Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #FFFFFF;;">End Commitment Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #FFFFFF;;">Monthly Cost USD</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Committed Spend Jul-Dec 11 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #FFFFFF;;">Months</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #000080;background-color: #FFFFFF;;">Days</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">7/30/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">29,032.26</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">29,032.26</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">7/31/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6/1/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/1/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">30,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/5/2010</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/4/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">15,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">16,935.48</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">15,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1,935.48</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2/20/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/19/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">40,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">64,516.13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">40,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">24,516.13</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2/20/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/19/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">40,001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">64,517.74</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">40,001.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">24,516.74</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8/10/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11/29/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">40,000</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">158,666.67</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">120,000.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">38,666.67</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">*******************</td><td style=";">*****************</td><td style=";">****************</td><td style=";">*****************</td><td style=";">**********</td><td style=";">**********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=(<font color="Blue">DATEDIF(<font color="Red">MAX(<font color="Green">DATE(<font color="Purple">2011,7,1</font>),A2</font>),B2+1*(<font color="Green">DAY(<font color="Purple">B2</font>)=DAY(<font color="Purple">DATE(<font color="Teal">YEAR(<font color="#FF00FF">B2</font>),MONTH(<font color="#FF00FF">B2</font>)+1,</font>)</font>)</font>),"m"</font>)+IF(<font color="Red">DAY(<font color="Green">B2</font>)<>1,MOD(<font color="Green">(<font color="Purple">DAY(<font color="Teal">B2</font>)</font>)/DAY(<font color="Purple">DATE(<font color="Teal">YEAR(<font color="#FF00FF">B2</font>),MONTH(<font color="#FF00FF">B2</font>)+1,</font>)</font>),1</font>)</font>)</font>)*C2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=(<font color="Blue">DATEDIF(<font color="Red">MAX(<font color="Green">DATE(<font color="Purple">2011,7,1</font>),A2</font>),B2+1*(<font color="Green">DAY(<font color="Purple">B2</font>)=DAY(<font color="Purple">DATE(<font color="Teal">YEAR(<font color="#FF00FF">B2</font>),MONTH(<font color="#FF00FF">B2</font>)+1,</font>)</font>)</font>),"m"</font>)</font>)*C2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">DAY(<font color="Red">B2</font>)<>1,MOD(<font color="Red">(<font color="Green">DAY(<font color="Purple">B2</font>)</font>)/DAY(<font color="Green">DATE(<font color="Purple">YEAR(<font color="Teal">B2</font>),MONTH(<font color="Teal">B2</font>)+1,</font>)</font>),1</font>)</font>)*C2</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[Alex Barrosse]

Thanks, the formula works! I had to add a simple IF component to deal with end dates that are after 12/31/2011 but besides that, it worked perfectly. Thanks so much for your help.

 

[markmzz]

Thanks, the formula works! I had to add a simple IF component to deal with end dates that are after 12/31/2011 but besides that, it worked perfectly. Thanks so much for your help.

Thanks for the feedback.

Markmzz