Hi,
new to the forum so be gentle. ok deep breath....
I need help with a spreadsheet i have drawn up to keep track of bills.


In one column you can select whether the bill is
monthly,quarterly,semi-annual or yearly (using a dropdown list). In the
next column you can select the 1st month the bill is paid (another drop
down box, date in mmm format). The next column displays whether the
bill is payable this month based on todays date (either yes or no). So
it looks something like this:

TODAYS DATE

____________M,Q,SA,Y? ___1ST BILL MONTH ___PAYABLE THIS MONTH?
BILL1 _________SA ______________MAY ______________YES
BILL2 _________Q________________JULY _____________NO
BILL3 _________Y________________MARCH____________ NO

This last column is the one im having trouble with. I can get it to say
yes if the 1st bill month matches todays month, but writing a formula
that works out subsequent billing months based on the m,q,sa column and
then seeing if they match todays month is a real headache. they end up
being massive nested affairs of the form:
=IF(OR(TEXT(I5,"mmm")=TEXT(D9,"mmm"),TEXT(DATE(YEA R(D9),MONTH(D9)+3,DAY(D9)),"mmm")=TEXT(I5,"mmm")), "yes","no")
where I5 is todays date and D9 is the first bill month column. This one
just sees whether today matches the bill month or the bill month +3, so
you could see how +3, +6, +9 would become huge.

monthly and yearly are easy to sort out in a formula, because if a
bills monthly it will always be payable and if its yearly then its only
going to fall on the 1st bill month no matter what. So the problem is a
formula for semi annual and quarterly months.
Im getting myself tied up in arrays and index,match etc so i need to
take a step back and let a fresh set of eyes look at the problem.

cheers for listening to my rant :)


--
colonel-shoe
------------------------------------------------------------------------
colonel-shoe's Profile: http://www.excelforum.com/member.php...o&userid=34695
View this thread: http://www.excelforum.com/showthread...hreadid=544631