Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
flag date within a cell after 15 days have past? | Excel Worksheet Functions | |||
Is there a function to show future date | Excel Worksheet Functions | |||
How do I format the date to show mm/dd NOT m/dd? | Excel Worksheet Functions | |||
Show a date based on today | Excel Worksheet Functions | |||
Show a date based on today | Excel Worksheet Functions |