I have a training database that has column headings of various courses and
row headings of individual staff names. Cells contain the dates they
attended that training. (See below for an example)
I want to enter a formula which will colour the cell if the training is
overdue (three or more years from current date) Can anyone help? Many thanks
A B
1st Aid Fire

joe 10.01.06 9.12.07
mick 5.02.09 3.6.09

1. Select the cell/Range (say B2:J10).

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=AND(B20,DATEDIF(B2,TODAY(),"y")=3)

Please note that the cell reference B2 mentioned in the formula is the
active cell in the selection. Active cell will have a white background even
after selection

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"rachel" wrote:

I have a training database that has column headings of various courses and
row headings of individual staff names. Cells contain the dates they
attended that training. (See below for an example)
I want to enter a formula which will colour the cell if the training is
overdue (three or more years from current date) Can anyone help? Many thanks
A B
1st Aid Fire

joe 10.01.06 9.12.07
mick 5.02.09 3.6.09

Hi Rachel!

I logged in after a long long time. Your post is very simple. If you are
using Office - 2007, Use Conditional Formatting to get your desired results.
Try this option, if you will not be able to use this option, just let me
know, I will explain you in detail. But 1st try this option.

"rachel" wrote:

I have a training database that has column headings of various courses and
row headings of individual staff names. Cells contain the dates they
attended that training. (See below for an example)
I want to enter a formula which will colour the cell if the training is
overdue (three or more years from current date) Can anyone help? Many thanks
A B
1st Aid Fire

joe 10.01.06 9.12.07
mick 5.02.09 3.6.09