training Out of date formula required

[Manxy]

I would like to keep a record of people who are First Aid trained at work.
I currently have a list of peoples names going downwards, B3 to B48 and the
next column C3 to C48 indicates a Yes or No answer to show either they're in
date or out of date and two totals at the bottom have a formula stating the
combined figures. =COUNTIF(C3:C48,"Y")
=COUNTIF(C3:C48,"N")
The next column D3 to D48 states the date they were trained.

As the training dates are always different and the timescale of their
training runs out after a year, I would like the program to change the colour
of the cell date to yellow when the run out date is within 2 months, then
changes to orange when the run out date is within one month and any out of
date training to be coloured red. Any indate trained personnel be coloured
white.
The column would be D3 to D48 and I know there's an answer to this, but
don't know what question or where to look for the answer.
Can you help?

 

[Bob Phillips]

Use conditional formatting with formulae of

=$D3<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) [red]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) [orange]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) [yellow]

in than order

 

[Manxy]

Hi Bob, I'm struggling with this.
I selected cell D3 and went to Format and then to the conditional formatting
section.

Firstly, i tried to copy what you wrote and write it into the conditional
formatting area after I selected Formula Is, but unfortunately I had to write
it in as it wouldn't paste it.
Secondly, after copying the formula in the 3 different areas, I ended up
with a red background and changing the date before or after a week or month
or 2 month did not seem to change the colour of that one cell

I'm obviously making some error, probably due to my lack of understanding in
this area, but I'm willing to have a bash.
Many thanks in replying quickly,
Manxy

Use conditional formatting with formulae of

=$D3<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) [red]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) [orange]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) [yellow]

in than order

--
__________________________________
HTH

Bob

I would like to keep a record of people who are First Aid trained at work.
I currently have a list of peoples names going downwards, B3 to B48 and
the
next column C3 to C48 indicates a Yes or No answer to show either they're
in
date or out of date and two totals at the bottom have a formula stating
the
combined figures. =COUNTIF(C3:C48,"Y")
=COUNTIF(C3:C48,"N")
The next column D3 to D48 states the date they were trained.

As the training dates are always different and the timescale of their
training runs out after a year, I would like the program to change the
colour
of the cell date to yellow when the run out date is within 2 months, then
changes to orange when the run out date is within one month and any out of
date training to be coloured red. Any indate trained personnel be coloured
white.
The column would be D3 to D48 and I know there's an answer to this, but
don't know what question or where to look for the answer.
Can you help?

 

[Bob Phillips]

Couple of things.

You can select all of D348, as long as D3 is the activecell, and apply it
en-masse using the formulae I gave.

You should be able to paste the formula, just don't include the [colour] bit
I added as explanation.

--
__________________________________
HTH

Bob

Hi Bob, I'm struggling with this.
I selected cell D3 and went to Format and then to the conditional
formatting
section.

Firstly, i tried to copy what you wrote and write it into the conditional
formatting area after I selected Formula Is, but unfortunately I had to
write
it in as it wouldn't paste it.
Secondly, after copying the formula in the 3 different areas, I ended up
with a red background and changing the date before or after a week or
month
or 2 month did not seem to change the colour of that one cell

I'm obviously making some error, probably due to my lack of understanding
in
this area, but I'm willing to have a bash.
Many thanks in replying quickly,
Manxy

Use conditional formatting with formulae of

=$D3<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) [red]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) [orange]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) [yellow]

in than order

--
__________________________________
HTH

Bob

I would like to keep a record of people who are First Aid trained at
work.
I currently have a list of peoples names going downwards, B3 to B48 and
the
next column C3 to C48 indicates a Yes or No answer to show either
they're
in
date or out of date and two totals at the bottom have a formula stating
the
combined figures. =COUNTIF(C3:C48,"Y")
=COUNTIF(C3:C48,"N")
The next column D3 to D48 states the date they were trained.

As the training dates are always different and the timescale of their
training runs out after a year, I would like the program to change the
colour
of the cell date to yellow when the run out date is within 2 months,
then
changes to orange when the run out date is within one month and any out
of
date training to be coloured red. Any indate trained personnel be
coloured
white.
The column would be D3 to D48 and I know there's an answer to this, but
don't know what question or where to look for the answer.
Can you help?

 

[Mike Fogleman]

The formula for yellow is incorrect, change -12 to -10 and then the yellow
will work for dates 8/18/2008 to 9/17/2008.

Mike F

Hi Bob, I'm struggling with this.
I selected cell D3 and went to Format and then to the conditional
formatting
section.

Firstly, i tried to copy what you wrote and write it into the conditional
formatting area after I selected Formula Is, but unfortunately I had to
write
it in as it wouldn't paste it.
Secondly, after copying the formula in the 3 different areas, I ended up
with a red background and changing the date before or after a week or
month
or 2 month did not seem to change the colour of that one cell

I'm obviously making some error, probably due to my lack of understanding
in
this area, but I'm willing to have a bash.
Many thanks in replying quickly,
Manxy

Use conditional formatting with formulae of

=$D3<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) [red]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-11,DAY(TODAY())) [orange]

=$D3<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())) [yellow]

in than order

--
__________________________________
HTH

Bob

I would like to keep a record of people who are First Aid trained at
work.
I currently have a list of peoples names going downwards, B3 to B48 and
the
next column C3 to C48 indicates a Yes or No answer to show either
they're
in
date or out of date and two totals at the bottom have a formula stating
the
combined figures. =COUNTIF(C3:C48,"Y")
=COUNTIF(C3:C48,"N")
The next column D3 to D48 states the date they were trained.

As the training dates are always different and the timescale of their
training runs out after a year, I would like the program to change the
colour
of the cell date to yellow when the run out date is within 2 months,
then
changes to orange when the run out date is within one month and any out
of
date training to be coloured red. Any indate trained personnel be
coloured
white.
The column would be D3 to D48 and I know there's an answer to this, but
don't know what question or where to look for the answer.
Can you help?

 

[Jacob Skaria]

Hi "Manxy"

Try the below few steps..

1. Select the range D348

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(D3<>"",D3<=TODAY())
Click Format Button>Pattern and select your color (say Red)

4. Click on Add button.

5. For Condition2>Select 'Formula Is' and enter the below formula
=IF(D3>TODAY(),DATEDIF(TODAY(),D3,"m")<1)
Click Format Button>Pattern and select your color (say Orange)

6. Click on Add button.

7. For Condition3>Select 'Formula Is' and enter the below formula
=IF(D3>TODAY(),DATEDIF(TODAY(),D3,"m")<2)
Click Format Button>Pattern and select your color (say Yellow)

8. Hit OK

PS: You have not mentioned the excel version. I have assumed that you are
using 2003. If you are using 2007 from menu Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format..Try and feedback

If this post helps click Yes

 

[Manxy]

Hi Jacob, thank you for showing me the step by step order as this is hugely
appreciated and probably for others who read this.

Being the 19th of June 2009 today and using the cells D3 to D48
I checked the cells using different dates and it came up with this:

2008 dates and upto the 18 Jul 09, there was no colour
19th July 09 only, there was 1 date only and coloured red
20th July 09 to 18th August 09 the colour was orange
19th August 09 to 18th September 09 the colour was yellow
19th September onwards, there was no colour

This is on the right track for what I require
Basically, if I enter an old date say 6th Dec 08, that particular person
would be in date till 5th december 09, but on the 6th December, their
background of the date would turn red to indicate he is now out of date and
remain so until a fresh date has been entered.

Using the above date of 6th December 08, the background colour would be
orange if there was a 1 month gap between 6th November 09 to 5th December 09.
This colour is to indicate that the person needs to be retrained quickly so
that they remain indate

Again using the date entered of 6th December 08, I would like the background
colour to be yellow from 6th October 09 to 5th November 09. This colour would
indicate a warning to the coordinator that training will be required soon.

All dates that are within the 10 month time period, would remain white in
colour
Hopefully I've helped explain what I'm after and a few tweaks would be all
thats necessary to resolve this.
Many thanks for everyones input so far
Manxy
=======================================