I don't want to Excel, just get by...
March 8, 2011 1:18 PM Subscribe
My conditional formatting in Excel 2007 is at war with itself. Help me emerge the victor.
I am building a spreadsheet to track due dates. Thanks to the wonders of conditional formatting, I can make those due dates change color based on the date in the cell.
- 15 days or more until the due date, and the cell turns itself green. ("Format only cells with: Cell Value, greater than, =TODAY()+15")
- 8-14 days until the due date, and the cell turns itself orange. ("Format only cells with: Cell Value, between, =TODAY()+8 and =TODAY()+15")
- And once we're down to the last week, the cell turns itself red. ("Format only cells with: Cell Value, less than or equal to, =TODAY()+7")
According to the above formulas, past due tasks would remain a lovely shade of crimson, putting us on High Alert that something had better be done. But if something is done, I'd really rather call off the hounds and have it revert to some sort of cool, soothing blue. That's where the problems come in.
I have tried using relative referencing so that when I add a date upon which the task was actually completed in the neighboring column, the hitherto ANGRY RED CELL will turn a nice shade of blue. To no avail. Nothing seems to work. And this is such a simple If/Then statement that I'm sure the problem is me and not Excel.
To date, I have tried highlighting the two relevant cells (in this case, my two test cells are E4 and F4), and added a new rule for conditional formatting. Thanks to these other questions, and Google, I've tried:
Format values where this formula is true: =$F$4=yes
Format values where this formula is true: =$F4=yes
Format values where this formula is true: =NOT(ISBLANK(F))
Format values where this formula is true: =NOT(ISBLANK($F))
Format values where this formula is true: =$F=NOT(ISBLANK)
Format values where this formula is true: =$F$4=NOT(ISBLANK)
And a whole host of other little formulas that were all questionable in their construction, but seemingly what the internet was telling me to do.
Now it falls to you, m'dears, to rescue me from my own stupidity.
TL;DR Version:
The IT guys can't help me, but I'm sure the internet can.
I want one cell in Excel to color itself based on its own contents until I enter information into another cell, at which time I want that second cell to be in charge and color them both. Excel can do this, right? How?
I am building a spreadsheet to track due dates. Thanks to the wonders of conditional formatting, I can make those due dates change color based on the date in the cell.
- 15 days or more until the due date, and the cell turns itself green. ("Format only cells with: Cell Value, greater than, =TODAY()+15")
- 8-14 days until the due date, and the cell turns itself orange. ("Format only cells with: Cell Value, between, =TODAY()+8 and =TODAY()+15")
- And once we're down to the last week, the cell turns itself red. ("Format only cells with: Cell Value, less than or equal to, =TODAY()+7")
According to the above formulas, past due tasks would remain a lovely shade of crimson, putting us on High Alert that something had better be done. But if something is done, I'd really rather call off the hounds and have it revert to some sort of cool, soothing blue. That's where the problems come in.
I have tried using relative referencing so that when I add a date upon which the task was actually completed in the neighboring column, the hitherto ANGRY RED CELL will turn a nice shade of blue. To no avail. Nothing seems to work. And this is such a simple If/Then statement that I'm sure the problem is me and not Excel.
To date, I have tried highlighting the two relevant cells (in this case, my two test cells are E4 and F4), and added a new rule for conditional formatting. Thanks to these other questions, and Google, I've tried:
Format values where this formula is true: =$F$4=yes
Format values where this formula is true: =$F4=yes
Format values where this formula is true: =NOT(ISBLANK(F))
Format values where this formula is true: =NOT(ISBLANK($F))
Format values where this formula is true: =$F=NOT(ISBLANK)
Format values where this formula is true: =$F$4=NOT(ISBLANK)
And a whole host of other little formulas that were all questionable in their construction, but seemingly what the internet was telling me to do.
Now it falls to you, m'dears, to rescue me from my own stupidity.
TL;DR Version:
The IT guys can't help me, but I'm sure the internet can.
I want one cell in Excel to color itself based on its own contents until I enter information into another cell, at which time I want that second cell to be in charge and color them both. Excel can do this, right? How?
That is assuming your due date is in A2 and the date completed is in B2.
posted by soelo at 1:40 PM on March 8, 2011
posted by soelo at 1:40 PM on March 8, 2011
Wait sorry, make that =0<>
With the first formula, a blank B2 will turn the cell blue as well.
posted by soelo at 1:42 PM on March 8, 2011
posted by soelo at 1:42 PM on March 8, 2011
Try Conditional Formatting, New Rule, and the last option, 'Use a Formula'. Then combined the two conditions using AND, e.g.
=AND(A1>TODAY()+15,ISBLANK(B1))
will colour A1 as you choose when the value in A1 is more than 15 days away from today, adn there is no value in B1.
As is, this just checks that B1 has something in it, not that it is a valid date, you can add additional conditions to the AND formula if you want to have more restrictions on the value of B1.
Haven't tried this, but something like
=AND(A1>TODAY()+15,if(ISNUMBER(B1),if(B1>date(2010,1,1)TRUE, FALSE),FALSE))
would check that there is a number in B1 (dates are numbers in Excel, the count of days since 01/01/1905), and that it is greater than 2010 (to make it more likely it is a date).
The ISNUMBER nested IF is that otherwise it will throw an error if you try logical comparisons like > on a non-number.
posted by Boobus Tuber at 1:52 PM on March 8, 2011
=AND(A1>TODAY()+15,ISBLANK(B1))
will colour A1 as you choose when the value in A1 is more than 15 days away from today, adn there is no value in B1.
As is, this just checks that B1 has something in it, not that it is a valid date, you can add additional conditions to the AND formula if you want to have more restrictions on the value of B1.
Haven't tried this, but something like
=AND(A1>TODAY()+15,if(ISNUMBER(B1),if(B1>date(2010,1,1)TRUE, FALSE),FALSE))
would check that there is a number in B1 (dates are numbers in Excel, the count of days since 01/01/1905), and that it is greater than 2010 (to make it more likely it is a date).
The ISNUMBER nested IF is that otherwise it will throw an error if you try logical comparisons like > on a non-number.
posted by Boobus Tuber at 1:52 PM on March 8, 2011
Response by poster: Never used this service before, but below should be a link to the file that I'm working on. I've inserted a number of dummy dates just to demonstrate the way it should typically work.
http://wikisend.com/download/467206/sample spreadsheet due date tracking.xlsx
posted by jph at 2:27 PM on March 8, 2011
http://wikisend.com/download/467206/sample spreadsheet due date tracking.xlsx
posted by jph at 2:27 PM on March 8, 2011
Best answer: This worked for me:
posted by alyxstarr at 7:09 AM on March 9, 2011
=$F4<>""Since rules are applied in order, make sure it is the first rule on the list; otherwise your date rules will override it.
posted by alyxstarr at 7:09 AM on March 9, 2011
Response by poster: Sorry folks, none of these solutions are working for me.
posted by jph at 6:13 AM on March 10, 2011
posted by jph at 6:13 AM on March 10, 2011
Best answer: I got it to work using these rules in this order:
"=$F4<>"" " turns E4 blue (alyxstarr's formula)
"=$E4-TODAY()>15" turns E4 green
"=$E4-TODAY()>8" turns E4 orange
"=$E4-TODAY()<7" turns E4 red
also, I checked the "Stop if true" box for all formulas.
When copying it to other cells, make sure the "Applies to:"section updates correctly. That is always my downfall with Cond. Formatting. If that doesn't work, can you tell us if some of the rules seem to work and others don't?
posted by soelo at 10:55 AM on March 10, 2011
"=$F4<>"" " turns E4 blue (alyxstarr's formula)
"=$E4-TODAY()>15" turns E4 green
"=$E4-TODAY()>8" turns E4 orange
"=$E4-TODAY()<7" turns E4 red
also, I checked the "Stop if true" box for all formulas.
When copying it to other cells, make sure the "Applies to:"section updates correctly. That is always my downfall with Cond. Formatting. If that doesn't work, can you tell us if some of the rules seem to work and others don't?
posted by soelo at 10:55 AM on March 10, 2011
Response by poster: Okay, so here's the final resolution. Thanks everyone for your help. Soelo/alyxstarr's final formula for bluing finally worked but not with the other formulas offered.
In the end, I used the "format only cells that contain, cell value, is..." etc., just as I had above. The missing link was the (Format values where this formula is true =F4<>"") with "stop if true" checked on only that one formatting rule.
This yields a final product that automatically calculates final due dates based on a simple standard formula and then color codes those due dates based on how close or far those due dates are from the date that the file is viewed. Green for dates more than two weeks in the future, orange for dates between one and two weeks in the future, and red in the final week running up to the due date as well as all past-due dates... until something is entered into the cell adjacent to the due date denoting final action, at which time completed tasks turn blue.
Important to note: I had to tweak the final formula that was offered removing the $, since that anchored the column as F (and thus prevented me from applying the formula successfully across multiple paired columns in the spreadsheet, since I wanted all the other columns to refer relatively to the next column over, not necessarily back to F).
Anyway, anyone who wants to see the final product (or who thinks that color coded date tracking is useful and would like to use this spreadsheet), just MeMail me and I'll be happy to send it along to you.
posted by jph at 8:26 AM on March 11, 2011
In the end, I used the "format only cells that contain, cell value, is..." etc., just as I had above. The missing link was the (Format values where this formula is true =F4<>"") with "stop if true" checked on only that one formatting rule.
This yields a final product that automatically calculates final due dates based on a simple standard formula and then color codes those due dates based on how close or far those due dates are from the date that the file is viewed. Green for dates more than two weeks in the future, orange for dates between one and two weeks in the future, and red in the final week running up to the due date as well as all past-due dates... until something is entered into the cell adjacent to the due date denoting final action, at which time completed tasks turn blue.
Important to note: I had to tweak the final formula that was offered removing the $, since that anchored the column as F (and thus prevented me from applying the formula successfully across multiple paired columns in the spreadsheet, since I wanted all the other columns to refer relatively to the next column over, not necessarily back to F).
Anyway, anyone who wants to see the final product (or who thinks that color coded date tracking is useful and would like to use this spreadsheet), just MeMail me and I'll be happy to send it along to you.
posted by jph at 8:26 AM on March 11, 2011
« Older Why are the world's resources distributed the way... | Asking for trouble? Forms library on a non-profit... Newer »
This thread is closed to new comments.
posted by jkaczor at 1:21 PM on March 8, 2011