Reference old thread: training Out of date formula required:
7/18/2009 5:34 AM PST

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 using excel
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
=======================================
"Jacob Skaria" wrote:

Hi "Manxy"

Try the below few steps..

1. Select the range D3:D48

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=AND(D3<"",D3<=TODAY())
Click Format ButtonPattern and select your color (say Red)

4. Click on Add button.

5. For Condition2Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<1)
Click Format ButtonPattern and select your color (say Orange)

6. Click on Add button.

7. For Condition3Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<2)
Click Format ButtonPattern 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 tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format..Try and feedback

---------------

It is not clear what you need changed?

"Manxy" wrote:

Reference old thread: training Out of date formula required:
7/18/2009 5:34 AM PST

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 using excel
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
=======================================
"Jacob Skaria" wrote:

Hi "Manxy"

Try the below few steps..

1. Select the range D3:D48

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=AND(D3<"",D3<=TODAY())
Click Format ButtonPattern and select your color (say Red)

4. Click on Add button.

5. For Condition2Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<1)
Click Format ButtonPattern and select your color (say Orange)

6. Click on Add button.

7. For Condition3Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<2)
Click Format ButtonPattern 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 tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format..Try and feedback

---------------

Try the below

'For red
=AND(D3<"",DATE(YEAR(D3)+1,MONTH(D3),DAY(D3))<=TO DAY())

'For Orange
=DATEDIF(D3,TODAY(),"m")=11

'For Yellow
=DATEDIF(D3,TODAY(),"m")=10

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


"Manxy" wrote:

Reference old thread: training Out of date formula required:
7/18/2009 5:34 AM PST

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 using excel
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
=======================================
"Jacob Skaria" wrote:

Hi "Manxy"

Try the below few steps..

1. Select the range D3:D48

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=AND(D3<"",D3<=TODAY())
Click Format ButtonPattern and select your color (say Red)

4. Click on Add button.

5. For Condition2Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<1)
Click Format ButtonPattern and select your color (say Orange)

6. Click on Add button.

7. For Condition3Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<2)
Click Format ButtonPattern 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 tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format..Try and feedback

---------------

Expiry date and total
Hi Jacob, just wanted to say that the formula works great and would
appreciate your help again please.
ie, Using the different categories as stated below, I would like them to
total up to show at least people indate or out of date with their training.
I'm not sure if I should include people approaching 1 and 2 months before
they are out of date with their training as indicated by the orange and
yellow cell background as the results might become confusing? (What do you
think?)
As I have 3 columns of people D3 to D48, K3 to K48 and R3 to R30, I would
like the totals of the 3 columns to go into anywhere between R32 to R48. Can
this be done?

I probably haven't explained it properly, but will try to reply as quick as
I can if you want to ask me any questions.
=============================


"Manxy" wrote:

Reference old thread: training Out of date formula required:
7/18/2009 5:34 AM PST

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 using excel
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
=======================================
"Jacob Skaria" wrote:

Hi "Manxy"

Try the below few steps..

1. Select the range D3:D48

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=AND(D3<"",D3<=TODAY())
Click Format ButtonPattern and select your color (say Red)

4. Click on Add button.

5. For Condition2Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<1)
Click Format ButtonPattern and select your color (say Orange)

6. Click on Add button.

7. For Condition3Select 'Formula Is' and enter the below formula
=IF(D3TODAY(),DATEDIF(TODAY(),D3,"m")<2)
Click Format ButtonPattern 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 tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format..Try and feedback

---------------