Conditional format assistance

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

On cell C4, goto Conditional formatting and set conditions to:
Cell Value Is > greater than > =A1

Alternatively, you could remove the need for todays date in any particular cell and do it direct from Cond. Format... ie,
Cell Value Is > greater than > =TODAY()

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

It must be that the cell isnt truly blank... have you inserted a space, or perhaps does it contain a formula with an ,"") section.

If your dates are dead-figures and entered manually, make sure they are all definately blank.

If the date is generated by a formula that also returns "" if criteria is not met, you will need to change your conditional formatting to :
Formula Is > =AND(C4<>"",C4>TODAY())

or

Formula Is > =AND(C4<>"",C4>A1)