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.

 

[Peter_SSs]

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

I can see a couple of issues.

1. If the 'Day' for 'Current month' and 'Forecast date' are both greater than the 'due' day the result is one payment too many. For example, see the calculation for Electricity in your example. The only payments between 15 Jul and 23 Sep would be 13 Aug and 13 Sep so I think the result there should be 380.

2. Provided the OP didn't have in mind entering a date more than 12 months in advance (which may very well be the case) when they stated:

... from today() until MM/DD/YYYY.

Your example has also highlighted another possible problem (don't date questions throw up a lot of problems? ) for both our suggestions.

You have an example where the due day is 30. If that were the case, I'm sure in practice the Gas bill would still have to be paid in February even though February doesn't have 30 days. Yet both our solutions return 0 for the following data
Due day: 30
Current date: 1 Feb 2011
Forecast Date: 28 Feb 2011

 

[SpaceBolt]

Well lucky for me, I am never billed for any of my expenses on the 29th, 30th, or 31st.

I plan to use the "bill date", which is obviously the date that the company sends me a bill. Technically I could make the date that I actually pay the bill whatever date I want between the bill date and the due date. This helps me work around some potential problems.

 

[Peter_SSs]

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

For my suggestion in post #5 you would remove the = from the middle of the formula.

 

[SpaceBolt]

Also, I do want to be able to enter in dates years in the future. For instance, 2020. Of course, the farther away the date, the more I have to consider raises, inflation, emergency expenses, etc.

 

[Peter_SSs]

Well lucky for me, I am never billed for any of my expenses on the 29th, 30th, or 31st.

Also, I do want to be able to enter in dates years in the future.

Given those two facts, and that you want to exclude the current date from the calculation, I think the post #5 formula with the modification suggested in my last post should go pretty close I think.