You are here:
- Home
- Computing/Technology
- Microsoft Software
- Excel
- excel 2007 Conditional formatting for dates
Excel/excel 2007 Conditional formatting for dates
Advertisement
Question
I'm using Excel 2007. I'm building a tracking spreadsheet to track training dates. As people complete training, they report their completeion dates to me. i enter these into the excel sheet.
I want the sheet to change the cell fill color based on how old the date is. If the date occured within the last 300 days the fill color would be green. if the date occurred between 301 and 330 days ago it would be yellow. any date that occured after 330 days ago would be red.
Thanks,
dan
Answer
Hi Dan,
)
You will need to set up three cases to do this. In the Conditional Formatting dialog box, set it up for three cases and select the "Formula Is" option for all three. Use the following formulas for the three cases.
=B3>TODAY()-300
=B3>TODAY()-330
=B3<=TODAY()-330
Set their colors green, yellow and red respectively. I have a graphic of the completed dialog box that should help.
Doug Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com
Related Articles
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Answers by Expert:
- Nathan Head
- Chad Welch
- Philip Buckley
- Tushar Sakhalkar
- Bhaskar
- Amal
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bob Umlas
- Edward H. Mori
- Jessica L.
- Jan Karel Pieterse
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- Craig
- Adelaide carvalho
- Miguel Zapico
- JP
- Douglas M. Smith
- Richard Rost
- Ken Marron
- Victor Lan
- Bill
- Anne Troy
- Claire Blinman
Douglas M. Smith
Expertise
I specialize in solving formula, feature and structure related problems. I know many tricks to help make your spreadsheets and processing more efficient.
Experience
Brainbench MVP for MS Excel
Past/Present clients
Gannett, Fannie Mae, Pepsi, Nortel, Procter & Gamble, BellSouth, Blue Cross Blue Shield of NC, NC Central University, GlaxoSmithKline, Maintenance Excellence Institute, AAI Pharmaceuticals, Blue Cross Blue Shield of SC, Brainbench.com
©2009 About.com, a part of The New York Times Company. All rights reserved.