Last week when I was discussing the conditional formatting I did to highlight problem cases, I said I checked two things for the same condition. However, you can only enter one formula. So how did I do it? I combined the two things to check using AND.
There are three Boolean operators available in most spreadsheets: AND, OR, and NOT. They’re implemented as functions, which means the formulas to use them are =AND(thing1,thing2,thing3), =OR(thing1,thing2,thing3), and =NOT(thing). Of course, if you’re in OpenOffice instead of Excel or Google, you’d use semicolons instead of commas. If you want to look them up, they’re listed under the Logical category in the list of functions.
AND can have a long list of items, and for it to be true, every item in it has to be true.
OR can also have a long list of items, and for it to be true, at least one item in it has to be true. It doesn’t matter if more than one is true, as long as at least one is, so if AND(x,y,z) is true then OR(x,y,z) will also be true.
NOT can only have one item, and for it to be true that item has to be false.
You can also combine these functions in various ways. For example, if you wanted to make sure that everything in a list of things was false you could use =AND(NOT(x),NOT(y),NOT(z)), or you could use =NOT(OR(x,y,z)).
To understand how they work, imagine a shopping list. It might be something like “bread, milk, cheese, jam(any but grape), ice cream (strawberry/vanilla)”. Overall, this is a giant AND — you want bread AND milk AND cheese AND jam AND hot dogs. However, there’s an OR in it — you want the ice cream to be strawberry OR vanilla. Either one is good and if you find it’s on sale then you might pick up both, and you might pick up other flavors as well, but you need to get at least one of those two flavors. There’s also a NOT — you want the jam to be any flavor but NOT grape.
So the formula for this shopping list might be:
=AND(bread,milk,cheese,jam,NOT(grape jam),OR(strawberry IC, vanilla IC)
where each of those items is a cell that’s true if you’ve put the item in your cart.
Just like a math equation, you evaluate the inner parenthesis before the outer ones. The first set is NOT(grape jam). So if “grape jam” is true – i.e. there’s grape jam in the cart – then NOT(grape jam) is false and the whole result of the AND will be false. The second set of parenthesis is OR(strawberry IC, vanilla IC), so if you have strawberry or vanilla ice cream in the cart, that function will return true. So if both the NOT() and OR() are true, all that’s left is to check whether the other items (bread, milk, cheese, and jam) are true, and if they are then the AND() is true.
So, to bring it back to the conditional formatting in this spreadsheet, I used the formulas described below. For ease of reading I’m using named variables, although I didn’t name all the variables in the actual spreadsheet.
LastVisited is the date of the last time that teacher brought a class in.
SemesterStart is the starting date of the semester.
Since dates are stored as numbers and earlier dates are smaller numbers, LastVisited<SemesterStart is true if they haven’t visited since before the beginning of the semester, and false if they have.
TODAY is today’s date as far as the spreadsheet is concerned. It would normally be today(), but since it’s an example I wanted to be able to change what date the spreadsheet thought it was for easier demonstration.
15*7 is how many days there are in a 15-week semester, so 15*7/2 is half the days in a semester, so SemesterStart+15*7/2 is halfway through the semester. If you were doing this with a shorter or longer semester you’d just change the 15 to a different number.
So TODAY>(SemesterStart+15*7/2) is true if today’s date is more than halfway through the semester.
So the criteria for yellow, AND(LastVisited<SemesterStart,TODAY>(SemesterStart+15*7/2)), is true if it’s halfway through the semester, and they haven’t visited since before the beginning of it.
Similarly, (SemesterStart+12*7) is 3 weeks before the end of the semester, or 2 weeks before finals week. Again, if your semester is shorter or longer, change the 12 appropriately.
Therefore, AND(LastVisited<SemesterStart,TODAY>(SemesterStart+12*7)) is true if it’s been twelve weeks since the start of the semester, and they still haven’t visited.
Lastly, although the two rules both declare background colors for the cell and whenever the conditions for red are true the conditions for yellow are also true, I made sure that red would override yellow by following the rules for conflict resolution.