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

Bobby wrote:
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

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

"P Sitaram" wrote:


Bobby wrote:
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

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

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.

=(LEN(C4)*(C4<A1))

"P Sitaram" wrote:

=(LEN(C4)*(C4<A1))



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.

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