Quote:
Originally Posted by tarisea
Chaley the wording is seriously misleading and almost impossible to work with. I have run a few tests and this is what I figured out.
The condition is defining "not more days ago than" as any time in the future up to x number of days in the past from today.
|
Apologies, but I don't see the wording as misleading at all. A date in the future is "not more days ago" than any non-negative number.
I do see that it doesn't do what you want, and doesn't do what you originally asked for.
Quote:
The conditions needed is one that will equal any time in the past. Very simply "is less than today"
So basically on February 10, 2013:
4/2/2001 =IF(A1<TODAY(),true,false) TRUE icon
2/2/2012 =IF(A1<TODAY(),true,false) TRUE icon
11/10/2012 =IF(A1<TODAY(),true,false) TRUE icon
5/5/2013 =IF(A1<TODAY(),true,false) FALSE no icon
2/8/2013 =IF(A1<TODAY(),true,false) TRUE icon
|
Although that condition specification does what you want, it is as restrictive as the one I implemented. What happens if someone wants to know if the date is in the range from one month ago to one month from now? Older than a year ago? Between 6 months and a year from now? More than a year in the future?
I have added several more conditions that permit building ranges. The conditions are:
- is set -- true if the cell is not empty
- is not set -- true if the cell is empty
- is more days ago than N -- true if the date is less than (today - N days). By definition, dates in the future will not match.
- is fewer days ago than N -- true if the date is greater than or equal to (today - N days). By definition, dates in the future will match.
- is more days from now than N -- true if the date is greater than (today + N days). By definition, dates in the past will not match.
- is fewer days from now than N -- true if the date is less than or equal to (today + N days). By definition, dates in the past will match.
You can use these conditions to construct the range you want. For example, it appears that as you want any date in the past, you would use a rule with one condition: "is more days ago than 0" (or "is fewer days from now than 0" if 'today' is to match). If you want a range of dates in the last year but not the future, you would use two conditions: "is fewer days ago than 365" and "is fewer days from now than 0". If you want dates more than a year ago, use "is more days ago than 365". If you want a range of dates in the 60 days around today, use two conditions: "is fewer days ago than 30" and "is fewer days from now than 30". And so on.
Edit: changed the discussion to describe more precisely the matching rules. The "(and equal to)" clauses were added. Also note that the matching was changed from "24-hour days" to "calendar days". Only the date is used when matching. The time of day is ignored.