Hello everyone,

I have one column which contains only dates. What I am trying to do is
to add in conditional formatting which will compare the value in each
cell with today and two weeks later. If the date in the cell is within
2 weeks, color it yellow. If the date is today, color it red. I have
read some posts in the forum and followed them, but I encounter 3
problems:

(1) I can't seem to apply the conditions to an entire column. I have to
manually select the range of cells. Why I want to select the entire
column is so that when I add new values in later cells, the conditional
formatting straightaway applies to them.

(2) Blank cells are treated as true. When I select a range of cells and
input the formula, I notice that blank cells are treated as having
satisfied the condition and are highlighted yellow.

(3) Comparing the dates (the formula i use is "xxx-now()<14", where xxx
is the cell value) works in both directions (that is, it checks if the
date is within 2 weeks before or after). I am interested only in
comparing future dates, not past.

Any help is most appreciated.

Jason


--
jackalx
------------------------------------------------------------------------
jackalx's Profile: http://www.excelforum.com/member.php...o&userid=23766
View this thread: http://www.excelforum.com/showthread...hreadid=374145

select the entire column that you have the dates in - assume col C

in conditional formatting:

format backround to red and in condition select forumla is and type
in:
=AND(C1<"",DATE(YEAR(C1),MONTH(C1),DAY(C1))=DATE( YEAR(NOW()),MONTH(NOW()),DAY(NOW())))

create another conditional format with background yellow, select
forumla is and type in:
=AND(C1<"",DATE(YEAR(C1),MONTH(C1),DAY(C1))DATE( YEAR(NOW()),MONTH(NOW()),DAY(NOW()))-14,DATE(YEAR(C1),MONTH(C1),DAY(C1))<DATE(YEAR(NOW( )),MONTH(NOW()),DAY(NOW()))+14)

hth


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=374145