CONDITIONAL FORMATING QUESTION
 

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?


--
Kind regards
Megan

CONDITIONAL FORMATING QUESTION
 

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


Replace @mailinator.com with @tiscali.co.uk


"Megan" wrote in message
...
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?


--
Kind regards
Megan

CONDITIONAL FORMATING QUESTION
 

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

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


Replace @mailinator.com with @tiscali.co.uk


"Megan" wrote in message
...
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?


--
Kind regards
Megan

CONDITIONAL FORMATING QUESTION
 

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

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Megan" wrote in message
...
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" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Megan" wrote in message
...
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?


--
Kind regards
Megan