Conditional Format

S

Sibbs

Hi Guys, if someone could help I would be most grateful.

My problem is:

I have a sheet of course names in columns C to V.

Names of personnel down the left in column A with cels 6&7 merged, 8&9
merged, an so on. (Only column A has been merged in this way)

Each course has under it a completed and expires date. ie column c,
completed c6 and expires c7.

Column B just has COM and EXP below it beside each name to identify the dates

The date format is dd/mm/yy

What I would like is for the the expirey date to flag up a month in advance
with say the colour red.

Does that make any sense to anyone? I hope so.
 
M

Mike H

Hi,

If I've understood correctly yo have an expiry date in column b that you
want to turn (say) red a month before that date. Select your dates and this
assumes they begin in B1 and then:-

Format|conditional format.
Select formula is and paste this in and pick a colour
=AND(B1-TODAY()>=0,B1-TODAY()<=30)

Mike
 
B

Bob Phillips

Select yours cells in column C, assuming starting at C1, and use a CF
formula of

=AND(MOD(ROW(C1),2)=1,C1>=TODAY(),C1<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

Sibbs

Mike H said:
Hi,

If I've understood correctly yo have an expiry date in column b that you
want to turn (say) red a month before that date. Select your dates and this
assumes they begin in B1 and then:-

Format|conditional format.
Select formula is and paste this in and pick a colour
=AND(B1-TODAY()>=0,B1-TODAY()<=30)

Mike
Click to expand...
 
S

Sibbs

Worked a treat but for one thing. The dates that have already gone pst the 30
days dont stay highlighted. i.e if the course has already expired.
 
M

Mike H

use this one instead

=B1-TODAY()<=30
Mike

Sibbs said:
Worked a treat but for one thing. The dates that have already gone pst the 30
days dont stay highlighted. i.e if the course has already expired.
Click to expand...
 
S

Sibbs

Sorry Mike, maybe I am confusing the issue.

What I would like is for it to tell me 30 days in advance that the cousre
will expire and then if it does expire past the 30 days remain highlighted.

Sorry to be a pest
 
M

Mike H

The CF I just posted will highlight a day that is 30 days or less in the
future and the cell will remain highlighted after that even if the date goes
in the past.

Mike
 
Top