Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Traffic Light System
Hi there,
I operate a spreadsheet which requires a traffic light system of RED, AMBER and GREEN depending on the values within the spreadsheet. I'm having some difficulties formatting the sheet correctly to allow the appropriate cell to change colour. For example: Cell A2 contains the date on which the fault was reported. This is the date which the formula refers to. Cell F2 is a blank cell which needs to change colour based on how many days it has been since the fault was reported. 90 days and under is GREEN. 91 - 182 days are AMBER. 183 - 365 days are RED. I have found several formulas which would not allow either the AMBER or the RED to total up correctly. I have also found formulas which would allow this but only in Cell A2 where the date is stored and not F2 which is the blank cell with no value. Can anyone help? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Traffic Light System
Select Cell F2, format the cell green, and then choose Format / CF with the Formula is option.
Use a formula =AND(TODAY()=(A2+91), TODAY()<=(A2+182)) and format for amber, then add another condition, and use the formula is with =AND(TODAY()=(A2+183), TODAY()<=(A2+365)) HTH, Bernie MS Excel MVP "Custard Tart" wrote in message ... Hi there, I operate a spreadsheet which requires a traffic light system of RED, AMBER and GREEN depending on the values within the spreadsheet. I'm having some difficulties formatting the sheet correctly to allow the appropriate cell to change colour. For example: Cell A2 contains the date on which the fault was reported. This is the date which the formula refers to. Cell F2 is a blank cell which needs to change colour based on how many days it has been since the fault was reported. 90 days and under is GREEN. 91 - 182 days are AMBER. 183 - 365 days are RED. I have found several formulas which would not allow either the AMBER or the RED to total up correctly. I have also found formulas which would allow this but only in Cell A2 where the date is stored and not F2 which is the blank cell with no value. Can anyone help? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Traffic Light System
Hi there,
Thanks for the help but I'm a little confused by a few of your steps and have some further questions. Select Cell F2, format the cell green, Why would I format the cell green first and not choose it from the "patterns" option when I enter the formula? and format for amber Am I adding another condition and another formula for this or do you mean that the amber would then override the cell's normal green colour? Also, I'd like to know how to extend the formula for all of column A to be included if anyone adds extra dates and for all of column F to change based on this? Is there a way for column F to start out as white rather than be green by default? Thanks for your help! CT |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Traffic Light System
CT,
Forget about formatting the default green - use your white, and use a third CF with the formula =TODAY()<=(A2+90) You can try Tools / Options Edit tab, check "Extend data range formats and formulas" That option may work, if your columns F and A are 'connected' somehow. I don't find it to be reliable, so you can fall back on just copying the formatting down the column - the cells will be white until one of the conditions is met. HTH, Bernie MS Excel MVP "Custard Tart" wrote in message ... Hi there, Thanks for the help but I'm a little confused by a few of your steps and have some further questions. Select Cell F2, format the cell green, Why would I format the cell green first and not choose it from the "patterns" option when I enter the formula? and format for amber Am I adding another condition and another formula for this or do you mean that the amber would then override the cell's normal green colour? Also, I'd like to know how to extend the formula for all of column A to be included if anyone adds extra dates and for all of column F to change based on this? Is there a way for column F to start out as white rather than be green by default? Thanks for your help! CT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
traffic light in excel | Excel Discussion (Misc queries) | |||
traffic light formula | Excel Discussion (Misc queries) | |||
traffic light based on 3 different conditions | Excel Worksheet Functions | |||
Traffic light problem | Charts and Charting in Excel | |||
Traffic Light Filter On Dates | Excel Discussion (Misc queries) |