over due dates

Posted by naphelge on January 27, 2002 12:42 PM

Hi,

I have a worklog that has alot of info on different jobs I do for different customers. One of the entries is an invoice date. How can I use that invoice date to send an alert or highlight the entire tuple if the date is older than 6 weeks (i.e. overdue account / invoice)?

thanx

Posted by Aladin Akyurek on January 27, 2002 12:54 PM

Lets say that the invoice dates are in column D from D2 on.

Select all of your "tuples".
Activate Format|Conditional Formatting.
Choose "Formula is"for Condition 1.
Enter as formula:

=TODAY()-$D2>=42

or 30 (that is, 6*7=42 or 6*5=30).

Activate Format.

Select a color on the Patterns tab.

===========

Posted by naphelge on January 27, 2002 7:16 PM

Posted by naphelge on January 27, 2002 7:21 PM

Ok, I didn't think of a couple of things when I first wrote my question. So I have a column for invoice date & I have another column for payment rec'd date. How do I modify the formula below so that the payment rec'd date column is checked first & if there is no entry then it checks the invoice date column & compares if that date is >= 42 (days). Then can the entire tuple / row be highlighted so it's easy to see?

cheers,
naph

Posted by naphelge on January 27, 2002 7:35 PM

A formula something like...
= if ($V5 >= 0.01) today()-$L5 >= 42

This formula doesn't work, but a formula something like this I think is what I need.

$V5 = outstanding amount (which goes to $0.00 once a paid date & amount are entered into other columns)

$L5 = original invoice date

cheers,
naph

Posted by Barrie Davidson on January 27, 2002 7:44 PM

A formula something like...

Just change the formula in the conditional formatting to read:

=AND($V5 >= 0.01,TODAY()-$L5 >= 42)

Regards,
Barrie
Barrie Davidson