calculating the number of pay-dates before a bill is due?

[Judoman]

I'm trying to make up a budget for my family, and I'd like to have
Excel tell me how much $ from each paydate I need to set aside, in
order to meet my upcoming bills. So:

1. I'd like to be able to enter in the amount of an upcoming bill
(a1),
2. the due-date of an upcoming bill (b1),

3. then somehow have Excel calculate how many pay-days I will get
before that date (c1)

4. then I will use a formula =a1/c1 to tell me how much money I need
to put aside from each of my upcoming paydays.

Is this possible, to get such a formula for cell c1? I get paid on
Friday morning, every second week. My next upcoming pay is Friday the
12th.


thanks very much!

 

[Gary''s Student]

This is quite easy if we first construct a short table of paydates.

In C1 enter:
3/12/2010
In C2 enter:
=C1+14 and copy down thru C30. In C1 thru C30 we see:

3/12/2010
3/26/2010
4/9/2010
4/23/2010
5/7/2010
5/21/2010
6/4/2010
6/18/2010
7/2/2010
7/16/2010
7/30/2010
8/13/2010
8/27/2010
9/10/2010
9/24/2010
10/8/2010
10/22/2010
11/5/2010
11/19/2010
12/3/2010
12/17/2010
12/31/2010
1/14/2011
1/28/2011
2/11/2011
2/25/2011
3/11/2011
3/25/2011
4/8/2011
4/22/2011


In A1 we enter the due-date, say 4/25/2010
Finally in B1 we enter:

=SUMPRODUCT(--($C$1:$C$30>=TODAY())*($C$1:$C$30<=A1))

This produces 4, which is clearly the correct result.

Have a pleasant day!

 

[Joe User]

See responses in your thread in m.p.e.worksheet.functions.


----- original message -----

 

[OssieMac]

Good method of calculating but I would not use TODAY(). It returns 4 today
but in 2 weeks time it will return 3.

I would include a column to enter the date the bill arrives so that it
remains fixed and then replace TODAY() in the formula with the cell
reference. Following example uses D1 for the date the bill arrives.

=SUMPRODUCT(--($C$1:$C$30>=D1)*($C$1:$C$30<=A1))

 

[Joe User]

=SUMPRODUCT(--($C$1:$C$30>=D1)*($C$1:$C$30<=A1))

Minor observation: the double negation ("--") is superfluous in this
context. Without vetting the formula overall, the following syntax is
sufficient:

=SUMPRODUCT(($C$1:$C$30>=D1)*($C$1:$C$30<=A1))


----- original message -----