Conditional Formatting More Than 3 Conditions Based On Dates
Feb 8, 2009
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it).
My conditions are:
1. Turn the cell background colour red (action overdue) if:
Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1]
2. Turn the cell background colour orange (action required) if:
Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View 9 Replies
ADVERTISEMENT
Oct 11, 2013
I am trying to do some conditional formatting to highlight rows based on several conditions, I don't know if this is possible, but these are the criteria:
AAPL
tt
10/1/2013 11:14:15
GOOG
ll
10/1/2013 11:14:20
[code].....
I want to highlight a row if:
- the difference in time value is < 20 seconds
- the first symbol column is the same
- at least one, but not all of the rows meeting the above criteria contains a value in the second column from a given list (in this example, say tt is on this list)
So if done properly the logic would highlight the first and third rows, but would highlight none if none of the values in column two match my list.
I think the test criteria would go something like this, however I know that there are problems with my row references.
=and(abs($C2-$C3:$C65536)<0.0003,$A2=$A3,match($A2,Sheet1!A:A,0)>0)
View 2 Replies
View Related
Aug 3, 2009
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View 4 Replies
View Related
Mar 29, 2012
I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.
I am working in Excel 2010
View 1 Replies
View Related
Jul 19, 2013
I'm trying to get conditional formatting to change the cell color based on a few conditions.
I want the cells in column D to turn red if;
- The number in column B has a match in column A
- And the corresponding cell in column C is not blank
- And the corresponding cell in column D is blank
In the example below, cell D4 should be red
If there's a match (Column B to Column A) and;
- If cell in column C is not blank and cell in column D is not blank, do nothing.
If there's a match (Column B to Column A) and:
- If cell in column C is blank and cell in column D is blank, do nothing.
Here's the formula I attempted but didn't get it to work.
IF(AND(C2"",D2=""),MATCH(B2,$A$2:$A$15,0),IF((AND(C2"",D"")),"",""))
View 3 Replies
View Related
Aug 9, 2014
I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.
I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.
See the attachment : Event Milestones based on Go Date Uploadable.xlsx
View 10 Replies
View Related
Oct 7, 2008
I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:
Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:
Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry
Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)
Now here is what I have so far:
3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days
N3 = 913
N4 = 1065
Conditional Formatting on cell C21
Condition 1 - color Green
=$C21+$N$3>=TODAY()
Condition 2 - color Red
=$C21+$N$4<TODAY()
Condition 3 - color Yellow
=AND($C21+$N$3<TODAY(),$C21+$N$4>TODAY())
View 3 Replies
View Related
Jul 25, 2008
I am trying to use conditional formatting to color a cell based on a date. I have a date in cell "a1" as my base date (no formatting needed). I have dates in cells "a5:a10".
I need those dates (a5:a10) to change color based on the time from "a1".
Less than 1 year = no formatting.
Between 1 year and two years = yellow.
Over two years = red.
I have tried different settings / formulas in the conditional formatting option, but I can't make it work. Do I need to use visual basic to do this?
View 9 Replies
View Related
Feb 23, 2010
I have a spreadsheet that tracks publication dates and review dates. I would like the publication date to change color based upon its relationship with todays date. If the publication date is equal to or older than two years from today turn red, if it is equal to or older than a year and a half from today turn yellow. This seems like a pretty simple formula but I have tried several times and I can't seem to get it to work.
View 3 Replies
View Related
Jan 8, 2012
I have a simple due date spread sheet for tasks that need completing at work. I am able to make the 'Due date' cell turn red when it is equal to or less than todays date, using conditional formatting. No problem.
I also have a 'Date closed' cell which is populated with a date when the task is complete.
What I am struggling to do however is change the red 'Due Date' cell to Grey once the 'Date Closed' cell is populated with a date. In fact I would like the whole row to be greyed out once the 'Date Closed' cell is populated.
I also have an 'Open / Closed' cell, which tells me if a task is open or closed, being chosen from a drop down I installed. Could this be used as an alternative to the 'Date Closed' cell. ie If someone selects 'Closed' in cell A4 the whole A row goes grey.
View 4 Replies
View Related
Feb 9, 2013
Each item (row) in my sheet contains a logged date in column A, and a priority Level in Column G - which is via a data validation drop down (1 to 3).
What I would like to do is create a conditional format based on what the priority level is, and whether it has expired its allowed resolution time based on the logged date.
For example a Level 1 priority requires a 1 day fix, a level 2 requires a 5 day fix and a level 3 requires a 10 day fix.
If the priority level is a 1 and the logged date is over 1 day old i need the whole row to change colour. If the priority level is a 2 and the logged date is over 5 days old I need it to change colour, and if the Priority level is a 3 and the logged date is over 10 days old I need it to change colour. The colour can be the same each time, but I just need to know how to get it to trigger.
I think I have used an IF(AND( formula in the past, but cannot remember how to do it.
View 4 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Feb 3, 2014
I am trying to use conditional formatting to calculate when the date in the top column gets within 60 days from expiring to turn yellow, 30 days from expiring to turn orange and when it expires to turn red. The dates that are listed from row 5 down are when the classes were taken. the expiration date will be based on row 3. Some are annual,3 years and 5 years, etc. I am using Excel 2010....
View 10 Replies
View Related
Jun 6, 2014
I just cannot think through this conditional formatting statement.
The conditions:
-- If the current (todays) date is 8 working days after the created date, then highlight in orange
-- If the current (todays) date is 10 working days after the created date, then highlight in red
On other words...
-- The dates that will get highlighted in red are overdue for processing
-- The dates that become orange are approaching the overdue time
-- Any dates that are less than 8 working days are NOT highlighted at all
View 6 Replies
View Related
May 25, 2013
I have a sheet where i need to highlight cells C3:C140 based on approaching dates. The cells C3:C140 contain dates generated from another file and would be random approaching dates.
The conditions are
1) Approaching Weekends (Sat and Sun) = always turn red
2) 3 days after today = orange
3) 2 days after today = dark orange
4) Other days = light orange
View 11 Replies
View Related
Nov 22, 2011
I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
View 4 Replies
View Related
Jun 9, 2014
In the attached spreadsheet, the numbers in col A derived from another spreadsheet. The table D1:L21 is precaculated based on projection. I need 2 cells highlighted when they capture the number from the same row (col A). In another word, whenever the number from col A fall in between the 2 cells in the same row, they will be highlighted. Another condition is when it is smaller then 10 reps, the corresding cell in col D will be highlighted, as demonstrated in the attached table. I tried AND(E2<A2, F2>A2) but it didn't work. I also tried LARGE((E2:L2<A2)*(E2:L2),1) and similar SMALL function. They work as an array in normal excel, but not in the conditional formatting.
View 5 Replies
View Related
Jun 27, 2013
I am trying to fill a cell green if the current value > avg value and in red if < than avg value. I have tried to use conditional formatting but I cannot see where i can add additional rules. In any case here is what I am trying to do:
Daily Avg. Units:42
Avg Bgt. Units:7
The cell containing the number 42 is filled in green. If the figure was less than 7 it would have been red.
View 11 Replies
View Related
Mar 6, 2008
Why is there a hard limitation like this? Is there a way to go around it? By having a different format for the cells outside the 3 conditions I can get 4 different formats altogether, but that's not really that much.
View 14 Replies
View Related
Jan 23, 2010
I'm using Excel 2000 which has a limit of three conditions, I have 6. If you could just get me started, I still don't understand VBA enough to do this.
In cell A1, the color is set to red. I want A1 to turn green when all 6 conditions are met:
1. Cell B1 has a valid value from its pull down list, no other value, and not empty.
2. Cell B2 has an integer, no decimal places allowed, and not empty.
3. Cell B3 has a valid value from its pull down list, no other value, and not empty
4. Cell B4 is not empty.
5. Cell B5 has a three decimal place number greater than zero.
6. Cell B6 has a single letter from A-Z only.
View 9 Replies
View Related
Jan 26, 2007
I have tried it, and it isn't changing the cells when I test the macro. I'm obviously doing something wrong, but can't figure out what....
View 9 Replies
View Related
Oct 17, 2008
I am using a drop down list in excel and want to conditional format the cells based on what is selected from the list. I can't use conditional formatting as I have more than 3 items in the list.
I am guessing VBA can help but am a complete novice so could use some handholding on what to do.
Idea is that if someone select "Good" from dropdown, cell becomes green, "Stable" is amber and so on.
View 9 Replies
View Related
Jul 10, 2009
Is it possible to apply confitional formatting on 6 conditions ...
View 9 Replies
View Related
Jan 8, 2010
I want to use conditional formatting for RAG reporting. I had done this by having Cell is R then the format would be RED, Cell is A, then yellow, etc...
However I would like to have a blank cell or N/a no format the cell at all-currently this is making the cells green.
Do I need to use Formula instead? and tips on how this could be written would be great as well.
View 9 Replies
View Related
Oct 31, 2006
I am trying to colour cells depending on the text in the cell. The text includes letters and symbols (e.g. A+ or C). The problem is I have five different conditions and Excel will only let me set up three.
The conditions I require are:
Text: A+ or A Colour: Pink
Text: A- or B+ Colour: Light Orange
Text: B or B- Colour: Sea Green
Text: C+ or C Colour: Lavender
Text: C- or D Colour: Brown
View 5 Replies
View Related
Jan 26, 2007
Need to figure out a code for Conditional formatting to work for 4 cases. I dropped programming 5 yrs ago...have forgotten mostly everything but I know this can be done. What I need:
Range = entire sheet
Upon entering a date(X) anywhere in the row the date is calculated from today's date(Y) and if the date entered is -infinity to 30 then the entire row turns red. 31 to 60 days orange, 61 to 90 days yellow, 91 to 120 days green, 120+ nothing.
Basically Y-X.
Case <30 red row
Case 31-60 orange row
Case 61-90 Yellow row
Case 91-120 Green Row
121+ nothing.
View 2 Replies
View Related
Apr 2, 2007
I'm trying to add conditional formatting to a column of data but there are more than 3 conditions. Is there a way of increasing the number of conditions you are able to set using the conditional formatting wizard or, if not, is there a way of applying conditional formatting using VBA ?
View 3 Replies
View Related
Aug 10, 2007
I am running Excel 2003 and when I open the file, I get the error: Invalid procedure call or library. When I check the references, it has
Microsoft Excel 11.0 object library
Microsoft Office 11.0 object library
and I manually added
Microsoft Outlook 11.0 object library
Not only do I get the same error, but when I try to save, it just gives me the totally unhelpful error message: File not saved
View 8 Replies
View Related
Nov 6, 2008
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
View 2 Replies
View Related
Jun 10, 2009
I have a spreadsheet that contains a great deal of information, though most if it contains dates. There are some colums that contain the word "NEED" if i don't yet have the date to put in there, and the cell is yellow. What i want to do is set up conditional formatting to change the cell to pink if:
A)the cell contains the "Need"
and
B)The date in the corresponding row from column C is more than 15 days old.
View 14 Replies
View Related
