Excel 2007 advanced functions?
I need a formula or function that will check a range of dates and then return a color for that cell.
like 0-30 days = white, 30-60 days = amber, 60-90 days = red
ex:
row column A B C
1 | 02/01/2011 | current date | (red colored cell) |
2 | 03/01/2011 | current date | (amber colored cell) |
3 | 04/03/2011 | current date | (white colored cell) |
ok i got to this function:
=IF(H2=”done”,”green”,IF(AND(N2<=59,N2>=0),”white”, IF(AND(N2>=60, N2<=89),"amber", IF(N2>=90,”red”))))
but now i just need the colors to work. So how can i make the “green”,”amber”,and”red” not just text but actual colors to fill the cells.
Hi,
You won’t be able to change the cell fill via the use of a formula unless you write a user defined function.
The easiest way to do what you’re after and link it into the formula you already have is by using conditional formatting (Home Tab>Conditional Formatting). Highlight the range you need then select to add a new rule.
The rule’s would be something along the lines of if the cell value equals “red” then fill the cell red; if the cell value equals “amber” then fill the cell amber etc.
Hope this makes sense.
Cheers and let me know if you need anything further.
F1 2011 Formula 1 | Go Compete trailer (2011)
|
|
Formula Vol. 1 (Deluxe Edition with 5 Bonus Tracks & DVD) $17.64 The deluxe edition of Formula Vol. 1 includes 5 extra songs and a bonus DVD. The extra songs are “Aleluya” (Feat. Pitbull), “Malevo,” “Promise” (English version feat. Usher), Aleluya (English version feat. Pitbull) and “Vale La Pena El Placer.” The DVD features the “You” music video and behind-the-scenes footage. Track Listing: CD: 1. Intro (Fórmula) (Feat. George Lopez) 2. La Diabla 3. Que Se Mu… |
|
|
Praxis II: Mathematics Content Knowledge Test, 0061 (CliffsTestPrep) $12.00 Your guide to a higher score on the Praxis II?: Mathematics Content Knowledge Test (0061)Why CliffsTestPrep Guides?Go with the name you know and trustGet the information you need–fast!Written by test-prep specialistsAbout the contents:Introduction* Overview of the exam* How to use this book* Proven study strategies and test-taking tipsPart I: Subject Review* Focused review of all exam topics: ari… |
|
|
electrical safety taboo formulas $81.17 … |
|
|
Excel 2007 spreadsheet formulas and functions chart: Value Professional $83.44 … |
|
|
TAG Heuer Men’s CV2A10.BA0796 Carrera Automatic Chronograph Watch $3,095.00 When you think of luxury timepieces, you should be thinking of the TAG Heuer Men’s Carrera Automatic Chronograph Watch, a superbly wearable and masculine watch. This watch’s amazing presence begins with a 43.5mm brushed and polished stainless steel case, sapphire crystal exhibition back and a black aluminum bezel, which features a tachymeter with white markers. The right side features a crown with… |