A cell with today's date doesn't help too much, since you can always get
today's date with the NOW() function. What we need is some date associated
with the entry itself, such as the date the Incident took place ... but there
is a problem with that which I'll discuss in a minute. Much better would be
a "Due Date" entry and/or a Completed Date entry.
Lets say that you have the date of the incident in column A, then a formula
like this will tell you how much overdue they are (assumes we're working on
row 2) :
=INT(NOW()-A2) & " days overdue"
a more general formula to cover larger periods:
=IF(NOW()-A2>60,"over 60",IF(NOW()-A2>30,"over 30",""))
You could nest more IF statements to break it down finer as:
=IF(NOW()-A2>60,"over 60",IF(NOW()-A2>30,"over 30",IF(NOW()-A2>20,"over
20",IF(NOW()-A2>10,"over 10","overdue"))))
[that would not have a line break in it when put into a cell]
THE PROBLEM: If all you have is today's date and the date the incident took
place, then each incident is only going to get older with passing time.
You're never able to exclude an incident because you are done with it -
there's no way to tell when you're finished with it. And maybe that's ok, if
all you want to know is how old it is.
So lets say that in column C you would enter a "date completed" date for an
incident, or you'd leave it empty for incidents you're not done with. Then a
general formula to test if you're done with one or not would be:
=IF(C2="","not a date","is a date")
and we can modify that with the earlier formula to tell you if an incident
is completed, or if not, how overdue it is (based on original entry date in
column A)
=IF(C2="",IF(NOW()-A2>60,"over 60",IF(NOW()-A2>30,"over
30",IF(NOW()-A2>20,"over 20",IF(NOW()-A2>10,"over
10","overdue")))),"Completed")
If you took it a step further and had a "Due Date" in column B, then you
could rewrite that last formula like this:
=IF(C2="",IF(B2<NOW(),IF(NOW()-B2>60,"over 60",IF(NOW()-B2>30,"over
30",IF(NOW()-B2>20,"over 20",IF(NOW()-B2>10,"over
10","overdue")))),""),"Completed")
This last one would show empty cell if it wasn't overdue or completed, will
show "Completed" if it has been completed, and will show timeframe overdue
for all others.
Hope this helps some.
