accounts over 90 days

[Donna Evans]

I am new to excel. I need a formula to calculate on my
statments everything in over 90, over 60, over 30, and
current . I have tried various forumula but continue to
come up with 0.00 or value error.
2/1/03 Invoice No: 5456 1,000.00
5/1/03 check no: 1234 500.00 500.00







30 days 30-60 days 60-90 days over 90 days

 

[Alan Beban]

I'm not proposing a solution for your particular question, but for what
it's worth, were I doing this in Excel, I would maintain a "database"
with at least the following fields for every entry (not all used for
every entry):

Date Activity Invoice No. Check No. Dr. Cr. Balance

Every entry would have a date, invoice no., debit or credit amount and
balance amount. Activity entries could be Invoice, Payment, Other
debit, Other credit. That way you could have easy and useful filters
and sorts.

I'm not considering a solution to your particular question, partly
because I can't tell how many columns you have and what's in them.

Alan Beban

 

[jr]

Use:
assuming invoice date is in F1, use:

=IF(NOW()-F1>90,"over 90",IF(NOW()-F1>60,"over 60",IF(NOW
()-F1>30,"over 30","current")))

perhaps your formula gave you zeros because a test gave a
TRUE result too soon. This is why the greatest
delinquent period (90 days) is first ... because if 30 is
used, the result is TRUE and the rest of the IF
conditions are ignored

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/ageing.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

Ageing analysis of data without using VBA
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"