need to show when date is past due

R

rsdunphy

I need to highlite a cell that has a date and is over due 365 days or more
from todays date. Having trouble with conditioonal formatting
 
D

David Billigmeier

In the conditional formatting box choose "Formula Is" and enter this:

=DAYS360(A1,TODAY())>365

Of course, change the 'A1' reference to whichever cell you are currently in.
 
R

Roger Govier

Hi David

I wondered why you use days360?

Surely it would be more accurate to use
=(TODAY()-A1)>365

Regards

Roger Govier
 
J

JE McGimpsey

Note that DAYS360() will give inaccurate answers due to its using 30 day
months. For instance

A1: 9/23/2004
A2: =DAYS360(A1, TODAY())

A2 returns 365 for today (27 September 2005) even though the actual
interval is 370 days.

Better to use

=(TODAY()-A1)>365

instead.
 
M

Myrna Larson

It certainly would! With today being Sep 28, 2005, and the prior date being
Sep 27, 2004, simple subtraction of the two dates gives 366, which, according
to the OP's requirements, should be overdue. DAYS360 gives 361, which is not
overdue.

DAYS360 is intended for financial calculations such as bond interest, etc,
where one assumes a year consists of 12 30-day months, or 360 days.
 
M

Myrna Larson

If you want to flag dates that are more than a year ago, and still use
DAYS360, then you should compare the result with 360 rather than 365.
 
Top