Calculating # of bill dates between today and a target date

[SpaceBolt]

So I am creating a financial forecasting spreadsheet for myself. I'm stuck on the last part. Let's say that on the 17th of every month I need to pay my cable bill. I'd like to be able to calculate the amount of times that I'll have to pay this bill from today() until MM/DD/YYYY.

Example:

- my cable bill is $50
- I have to pay it on the 17th of every month
- the target date is 9/23/2011 (the user enters in whatever target date they want)

..the formula should tell me that I will have to pay $150 ($50*3) for my cable expense between today (which is 7/15/2011) to 9/23/2011.

Any advice would be greatly appreciated!!

Thanks.

 

[AlphaFrog]

<br /><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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Bill</td><td style="font-weight: bold;text-align: center;;">Due Day</td><td style="font-weight: bold;text-align: center;;">Target Date</td><td style="font-weight: bold;text-align: center;;">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">$50 </td><td style="text-align: center;;">17</td><td style="text-align: center;;">9/23/2011</td><td style="text-align: center;;">$150 </td></tr></tbody></table><br /><br /><table 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">TODAY(<font color="Green"></font>),C2,"m"</font>)+(<font color="Red">DAY(<font color="Green">TODAY(<font color="Purple"></font>)</font>)<=B2</font>)-(<font color="Red">DAY(<font color="Green">C2</font>)<B2</font>)</font>)*A2</td></tr></tbody></table></td></tr></table><br />

The DATEDIF Worksheet Function

 

[SpaceBolt]

You sir, are godly.

Thank you so much.

 

[Peter_SSs]

...
The DATEDIF Worksheet Function

I was thinking along the same lines but I don't think it is very robust.

I have added a 'Today' column to do some testing and altered your formula to pick up the 'current' date from E2 rather than from the TODAY() function. The result for these dates should be 50 (1 payment on 17 Sep).

<b>SpaceBolt</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60px;" /><col style="width:70px;" /><col style="width:89px;" /><col style="width:56px;" /><col style="width:88px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:center; ">Bill</td><td style="font-size:10pt; text-align:center; ">Due Day</td><td style="font-size:10pt; text-align:center; ">Target Date</td><td style="font-size:10pt; text-align:center; ">Amount</td><td style="font-size:10pt; text-align:center; ">Today</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:center; ">$50 </td><td style="font-size:10pt; text-align:center; ">17</td><td style="font-size:10pt; text-align:center; ">30/09/11</td><td style="font-size:10pt; text-align:center; ">$0 </td><td style="font-size:10pt; text-align:center; ">31/08/11</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=(DATEDIF<span style=' color:008000; '>(E2,C2,"m")</span>+<span style=' color:008000; '>(DAY<span style=' color:#0000ff; '>(E2)</span><=B2)</span>-<span style=' color:008000; '>(DAY<span style=' color:#0000ff; '>(C2)</span><B2)</span>)*A2</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4 </a>

 

[Peter_SSs]

Perhaps this slight modification?

Excel Workbook
	A	B	C	D	E
1	Bill	Due Day	Today	Target Date	Amount
2	$50	17	16/07/11	23/09/11	$150
SpaceBolt

 

[SpaceBolt]

Well It looks good to me! Let me test it out a bunch.

 

[AlphaFrog]

Nice catch Peter.

 

[snoopyhr]

Here is my 2 cents on this, seems to be working ..

Excel Workbook
	A	B	C	D	E	F
1	Items	due	Amount	Current month	Date to forecast	Budget
2	Cable Bill	15	$ 50.00	7/15/2011	9/23/2011	$ 150.00
3	Telephone Bill	23	$ 90.00	7/15/2011	9/23/2011	$ 270.00
4	Gas Bill	30	$ 100.00	7/15/2011	9/23/2011	$ 200.00
5	Electricity	13	$ 190.00	7/15/2011	9/23/2011	$ 570.00
Sheet3

 

[SpaceBolt]

This forum is incredible, lol. Thanks to all of you!

 

[SpaceBolt]

Here is my 2 cents on this, seems to be working ..


Sheet3

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:64px;"><col style="width:64px;"><col style="width:89px;"><col style="width:100px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">Items</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">due</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">Amount</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">Current month</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">Date to forecast</td><td style="text-align:center; border-top-style:solid; border-bottom-style:solid; border-top-width:1px; border-bottom-width:2px; border-top-color:#000000; border-bottom-color:#000000; ">Budget</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Cable Bill</td><td style="text-align:right; ">15</td><td style="text-align:right; "> $ 50.00 </td><td style="text-align:right; ">7/15/2011</td><td style="text-align:right; ">9/23/2011</td><td style="text-align:right; "> $ 150.00 </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Telephone Bill</td><td style="text-align:right; ">23</td><td style="text-align:right; "> $ 90.00 </td><td style="text-align:right; ">7/15/2011</td><td style="text-align:right; ">9/23/2011</td><td style="text-align:right; "> $ 270.00 </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Gas Bill</td><td style="text-align:right; ">30</td><td style="text-align:right; "> $ 100.00 </td><td style="text-align:right; ">7/15/2011</td><td style="text-align:right; ">9/23/2011</td><td style="text-align:right; "> $ 200.00 </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Electricity</td><td style="text-align:right; ">13</td><td style="text-align:right; "> $ 190.00 </td><td style="text-align:right; ">7/15/2011</td><td style="text-align:right; ">9/23/2011</td><td style="text-align:right; "> $ 570.00 </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F2</td><td>=C2*IF(DAY(E2)>=B2,MONTH(E2)-MONTH(D2)+1,MONTH(E2)-MONTH(D2))</td></tr></tbody></table></td></tr></tbody></table>

To change your formula around to NOT include today in the calculation, I would just change the >= to >, correct?