How to highlight cells if it passes a set date?

[Gareth]

Hi all

I have 100+ clients on excel, all have certain certificates with different
expiry dates.

What I would like is either of the two.

1: The to highlight in red say if the expiry date is within 30-60 days of
todays date.
2: Have a seperate worksheet available whereby it will update automatically
with the name and certification that will expiry and of course when.

Obviously the reason for this is to ensure I remain compliant and chase
those candidates with soon to expire qualifications.

Thanks in advance
G

 

[Pyrite]

To answer the date question you could use a conditional format on the cell
containing the date (A1 in this example). Set the condition to Formula Is and
then enter =$A$1-30<TODAY() and then set the formatting to whatever you
require i.e. Bold and Red.

This will then highlight the date when it comes within 30 days of today's
date.

Hope that helps.

 

[Gareth]

Hi Pyrite

thanks for your quick response.

I cant seem to find conditioning format. I have added conditional formatting
to the ribbon, however when selected, this only gives options on things like
highlight cells
top/bottom rules,, data bars etc. nor is there anywhere that says Forumla Is.

any clues?

G

 

[Pyrite]

Highlight all the 'expiry date' cells (hold Ctrl whilst left click each if
they are split up) and then select 'Format' from the menu and then
Conditional Formatting. On the left of the dialogue box there is options to
select if the cell value is greater than etc, use the drop down that
currently says Cell Value Is and change this to Formula Is then input the
formula, it will then apply to those cells. The thing to be careful of is
that you need each date cell to refer to itself otherwise it may change when
the first one is within 30 days etc.

That is for Excel 2003. Let me know if you are not using that. Otherwise I
hope that makes more sense and you can now implement it.

 

[Pyrite]

To elaborate a little more, in cell A1 you need to make sure that the formula
in the conditional formatting reads A1 and then in A2 it must read A2 etc.

If you dont have dates in the range already you could format the first cell
to conditonal format and then simply copy down the row. If you intend to do
this change the formula slightly by removing the $ symbols, like this
=A1-30<TODAY()

When you copy this down it should change the cell reference automatically.
To check this just select a few individual cells at random and open the
conditional formatting dialogue box via the Format menu and make sure they
refer to themselves. You can also test by putting dates in that should react
and ones that shouldnt.

 

[Gareth]

Hi there Pyrite, thanks for your messages, trying to get my head around it.
I am using Excel 2007.

I am trying to put in the following formula

=$E$2:$E$34-30<TODAY()

I am using colum E to put all the dates in.

Obvioulsy what i want is excel to highlight the cell which is within 30 days
(or whatever number of days) of todays date.

the above formula doesnt seem to do anything.

any clues?

Alternativly, is there a way to enter the formula in yourself and email me a
copy so i can see what you have done

Thanks

 

[Gareth]

As a further note, regarding the formula i have just written, if I changed
the first cell then all other cells turn green. So formula does work in a way
but obvioulsy i need individual cells to be highlighted.

 

[David Biddulph]

The CF formula you want in row 2 is =$E2-30<TODAY()

Either select the whole range (with the active cell being in row 2) and then
insert the above as your CF formula and it will update the formula for
susequent rows.
Or Put the above condition in row 2 and use Format Painter to copy down
Or Put the above condition in row 2, copy that cell, and use Edit/ Paste
special/ Format to paste the format down your range.