Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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 "Manxy" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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 "Bob Phillips" wrote: 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 "Manxy" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
Couple of things.
You can select all of D3:D48, 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 "Manxy" wrote in message ... 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 "Bob Phillips" wrote: 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 "Manxy" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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 "Manxy" wrote in message ... 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 "Bob Phillips" wrote: 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 "Manxy" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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 If this post helps click Yes --------------- Jacob Skaria "Manxy" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
training Out of date formula required
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 ======================================= "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 If this post helps click Yes --------------- Jacob Skaria "Manxy" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help required in Formula for date | Excel Worksheet Functions | |||
Training date with filter | Excel Discussion (Misc queries) | |||
Date wise Sum Formula required | Excel Discussion (Misc queries) | |||
Date formula required | Excel Worksheet Functions | |||
addition to my date formula...required | Excel Worksheet Functions |