home finance’s

[dk]

I use Excel to keep a tab on home finance’s. Is it possible to have direct
debits to enter themselves on given dates? Or even a reminder to add them.
here is how it setup but doesn't seem to work , we get always the word
#value but no actual figures we have this idea from prog.

In cell A1, type Name
B1, type Date of DD
C1, type Amount
in D1 : O1, type Jan Feb Mar, etc
In cell A2, type Gas
B2, type 10
C2, type 5
In cell A3, type Electric
B3, type 15
C3, type 12
In cell A6, enter a test date E.g. 10/17/04

This should give you a basic layout with the Name of the DDs in Col A, the
Date of the month that they occur in Col B, the Amount you pay in Col C and
the Month Names in Cols D to O

Now for the formula:
In D2, type
=IF(DATEVALUE($B2&D$1)<=$A$6,$C2,0)
copy that formula into cells E2 : O2
Select cells D2 : O2 and copy the formula down into cells D3 : O3

Try it out by changing the date in cell A6 - more or fewer amounts should
appear as the date is increased or decreased

 

[Alan]

One way,
In B1 enter just the day of the month the debit is due, not the whole date,
in D1:O1 enter 1-1-2004, 1-2-2004 and custom format that range as ddd or
dddd
in D2 enter
=IF(DATE(2004,MONTH(D1),$B$2)<TODAY(),$C$1,0) and copy along to O2
Regards,

 

[R]

=IF(DATEVALUE($B2&"-"&D$1)<=$A$6,$C2,0)
DATEVALUE won't recognize the text you throw it unless it's in a standard
Excel format.
Your formula was asking it to evaluate "10Jan", which is not an excel date
format.
I changed it so the concatenation results in 10-Jan, which is a standard
format; it now works.

Ever look at Quicken or MS Money? <grin>

Rob
MCSE, Bremerton WA

 

[Rob]

=IF(DATEVALUE($B2&"-"&D$1)<=$A$6,$C2,0)
DATEVALUE needs as input a string that is in a standard format excel
recognises.