Conditional format assistance

B

Bobby

I have a spreadsheet with dates in cells that are future holiday dates, and i
would like the cells to change colour when the date has passed.
In A1 I have the formula for todays date... today() but would like some
assistance on how I would format C4 which is the first of my dates to enable
the cell to change colour when the date in C4 has passed.

Thanks in advance

Bobby
 
P

P Sitaram

Bobby said:
I have a spreadsheet with dates in cells that are future holiday dates, and i
would like the cells to change colour when the date has passed.
In A1 I have the formula for todays date... today() but would like some
assistance on how I would format C4 which is the first of my dates to enable
the cell to change colour when the date in C4 has passed.

Thanks in advance

Bobby
Click to expand...

select the cells of interest, i.e., say, C4:C100. go to
Format|conditional formatting , select Formula Is and enter the
formula:

=C4<A1 and set the formatting
 
B

Bobby

P Sitaram said:
select the cells of interest, i.e., say, C4:C100. go to
Format|conditional formatting , select Formula Is and enter the
formula:

=C4<A1 and set the formatting
Click to expand...
Thank you, that part works fine but how do I get the cells with no date in
them yet to stay clear.At the moment some cells have no dates in them yet and
they are turning to the colour I have formatted.
 
B

Bobby

P Sitaram said:
=(LEN(C4)*(C4<A1))
Click to expand...

Thanks for that it worked perfectly. How does that formula work?

I looked in the excel help pages for the LEN command but it was as much use
as a chocolate fire guard. It did not explain anything to me.
 
P

P Sitaram

LEN returns the length of the cell entry, giving 0 when there is none.
So, the formula can result in:
0*0 = 0 i.e., FALSE
O*1 = 0 -do-
+ve number *0 = 0 -do-
+ve number*1 = +ve number i.e., TRUE
 
Top