LinkBack URL
About LinkBacks
working on a sheet that tracks certification for people, and want the date of the last cert to turn red when it is 1 year old or older. any ideas?
using excel 2010
working on a sheet that tracks certification for people, and want the date of the last cert to turn red when it is 1 year old or older. any ideas?
using excel 2010
Select the range of cells you want to format, for example E2:E20. Click Conditional Formatting > New Rule > Use a formula to determine...
Set the formula to:
=E2<TODAY()-365
Click Format... and set the Fill color to Red. Click OK twice.
you could use a formula
=cell with date < TODAY()-365
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
the range of cells you want to format
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=cell with date < TODAY()-365
see attached
Format… [Number, Font, Border, Fill]
OK >> OK
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Welcome to the Forum DOCSLICE!
Not hard to do but the specific formula depends on how your data is organized. If you have each person in a row with their names in column A, and there is a different certification date in each column with the type of certification in row 1, then your dates start in B2. You will set up conditional formatting in the range B2:X9 where X is the last column and 9 is the last row. You will use a formula to decide which cells to format, and the formula will look like this:
Formula:
Please Login or Register to view this content.
It will be much easier to understand your problem if you provide your file. This allows us to see your data, layout, and possibly attach a file with a completed solution.
To post a file:
- Under the text box where you type your reply click the Go Advanced button.
- In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
- In the pop-up, click Add then Select to select a file from your local drive
- Click the Upload button to upload the file
- Click Done to attach it.
It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
I have tried this, and a few other options, but it keeps highlighting the ones that are current as well.
Note: posts above mine turn all dates to red if they are over a year old, mine does only the latest one, which is how I read the OP.
This did the trick! i just copy pasted your formula into my spreadsheet. Maybe the problem just existed between my hands and the keyboard. Thanks!Originally Posted by etaf
your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks