CONDITIONAL FORMATING QUESTION

M

Megan

IN MY SPR I HAVE A DATE COLUMN. HOW CAN I SET UP A CONDITIONAL FORMAT THAT
WILL TURN THE ROW INTO A COLOUR IF THE THE DATE IN THE COLUMN IS TODAYS DATE
OR A DATE IN THE PAST?
 
S

Sandy Mann

First of all I would turn off your Capitals lock, it is considered rude and
shouting to post in all capitals. Additionally, all capitals are harder to
read, especially for people whose first language is not English.

Now on to your problem. Conditional formatting applies to one cell only.
You therefore have to highlight the whole block of cells that you want to
change colour and the select Format > Conditional formatting. Change "Cell
value is" to "Formula is" and enter the formula:

=AND($A2<>"",$A2<=TODAY())


Assuming that the dates are in Column A starting in row 2. Then select the
formatting that you want. That will enterthe Conditional formatting in all
the highlighted cells

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
M

Megan

Sorry - I work mainly in capitals and didn't think to turn them off. No
offence intended.

The formula has worked thank you very much it was very helpful.

Am I correct in thinking that the "" part of the formula is relating to the
text at the end - today?

Also I notice you use an open bracket at the end ( I don't understand
this part. Can you let me know so that I know for next time?



Kind regards
Megan


Sandy Mann said:
First of all I would turn off your Capitals lock, it is considered rude and
shouting to post in all capitals. Additionally, all capitals are harder to
read, especially for people whose first language is not English.

Now on to your problem. Conditional formatting applies to one cell only.
You therefore have to highlight the whole block of cells that you want to
change colour and the select Format > Conditional formatting. Change "Cell
value is" to "Formula is" and enter the formula:

=AND($A2<>"",$A2<=TODAY())


Assuming that the dates are in Column A starting in row 2. Then select the
formatting that you want. That will enterthe Conditional formatting in all
the highlighted cells

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
S

Sandy Mann

Am I correct in thinking that the "" part of the formula is relating to
the
text at the end - today?
Click to expand...

No. Dates in Excel are just numbers formatted to look like dates. Try
entering a date and then re-formatting the cell tpo General and you will see
what Imean. If the cell is empty it has a value of zero, nor zero is
obviously smaller than any date so any empty cell would match the <= part of
the comparison and thus be coloured. To prevent this I checked that the date
cell was not empty (ie "").
Also I notice you use an open bracket at the end ( I don't understand
this part.
Click to expand...

There is no open bracket, the Date function in Excel is DATE() with an open
and closing bracket, the other bracket is the closing bracket for the AND at
the start of the formula, (ie ther are two Opening brackets and two Closing
brackets)

The formual is saying:

"If the cell in Column A is not empty and the date in that cell is before
today's date then colout the cell"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk


Megan said:
Sorry - I work mainly in capitals and didn't think to turn them off. No
offence intended.

The formula has worked thank you very much it was very helpful.

Am I correct in thinking that the "" part of the formula is relating to
the
text at the end - today?

Also I notice you use an open bracket at the end ( I don't understand
this part. Can you let me know so that I know for next time?



Kind regards
Megan
Click to expand...
 
Top