#1
|
|||
|
|||
Conditional Formatting Dates
Cell C2 has a future date in it (1/6/2015). Range N5:N51 holds various dates. I need to conditionally format the range with a formula like =(=<C2-1097), to format the cells for dates equal to or before 1/6/2012. What would the formula be? |
#2
|
||||
|
||||
Hi
select N5:N51 and use Code:
=$N5=<date(year($c$2-3),month($c$2),day($c$2))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi Pecoflyer, Thanks for your reply. I copied the formula into CF rules, formatted, and got: "The formula you typed contains an error......" I also tried it on a single cell, with the same result. Any idea what I could be doing wrong?
|
#4
|
|||
|
|||
Pecoflyer, Got rid of the error with: =$N5<=DATE(YEAR($C$2-3),MONTH($C$2),DAY($C$2)), but now all dates are coming up formatted - the formula is saying every date is TRUE, which is incorrect.
|
#5
|
|||
|
|||
SOLUTION: =$N5<=DATE(YEAR($C$2-3),MONTH($C$2),DAY($C$2)). The "-3" is interpreted as days in the YEAR expression. If "-3" is replaced with "1097", the formula works.
|
#6
|
||||
|
||||
Typo of mine it shoud be YEAR($C$2)-3 . Sorry about the mistake
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Not a problem. Thanks for the help.
|
Tags |
conditional formatting |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting/If Statement with Dates | AndrewEnos | Excel | 2 | 07-22-2014 07:34 AM |
Conditional Formatting - color only dates contained in a range | EC37 | Excel | 1 | 07-08-2014 12:51 PM |
Conditional Formatting - past due dates | namedujour | Excel | 1 | 06-27-2014 04:16 PM |
Conditional Formatting with Dates | namedujour | Excel | 1 | 02-21-2013 03:42 AM |
Conditional Formatting Expiration Dates Based on Text in Adjacent Cell | Frogggg | Excel | 1 | 10-25-2011 08:44 PM |