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

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

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

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