need to show when date is past due
 

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

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.


--
Regards,
Dave


"rsdunphy" wrote:

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

Hi David

I wondered why you use days360?

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

Regards

Roger Govier


David Billigmeier wrote:
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.

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.

In article ,
"David Billigmeier"
wrote:

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.

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.

On Wed, 28 Sep 2005 17:59:38 +0100, Roger Govier
wrote:

Hi David

I wondered why you use days360?

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

Regards

Roger Govier


David Billigmeier wrote:
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.

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.

On Wed, 28 Sep 2005 12:53:42 -0500, Myrna Larson
wrote:

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.

On Wed, 28 Sep 2005 17:59:38 +0100, Roger Govier
wrote:

Hi David

I wondered why you use days360?

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

Regards

Roger Govier


David Billigmeier wrote:
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.