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.

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

"Sibbs" wrote:

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.

"Mike H" wrote:

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

"Sibbs" wrote:

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.



The expire date starts at C7, D7, E7 and so on for each course against each personnel name, so the next personnel name would be C9, D9, etc. is just a case of highligting the expire row?

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.

"Mike H" wrote:

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

"Sibbs" wrote:

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.

use this one instead

=B1-TODAY()<=30
Mike

"Sibbs" wrote:

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.

"Mike H" wrote:

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

"Sibbs" wrote:

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.

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

"Mike H" wrote:

use this one instead

=B1-TODAY()<=30
Mike

"Sibbs" wrote:

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.

"Mike H" wrote:

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

"Sibbs" wrote:

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.

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

"Sibbs" wrote:

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

"Mike H" wrote:

use this one instead

=B1-TODAY()<=30
Mike

"Sibbs" wrote:

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.

"Mike H" wrote:

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

"Sibbs" wrote:

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.

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(TO DAY()),MONTH(TODAY())+1,DAY(TODAY())))

--
HTH

Bob

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

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