conditional formatting highlight

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting for Highlighting Dates | Excel forum

Conditional Formatting for Highlighting Dates | Excel forum

resolvedResolved · Medium Priority · Version 2013

Lisa has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course

Conditional Formatting for Highlighting Dates

Hi,

Can you please advise how I can get a date cell to change colour (be highlighted) to indicate an upcoming end date within say, the next calendar month? But not for dates beyond that?

Thanks,

Lisa

Edited on Tue 17 Jun 2014, 14:15

RE: Conditional Formatting for Highlighting Dates

Hi Lisa

Thanks for getting in touch. You'll need to use a specific type of Conditional Formatting to achieve this. The following rule will highlight any date that is less than (before) today's date plus thirty days.

Select your date cells, then on the Home tab go to Conditional Formatting > New Rule.

Choose Format only cells that contain:

Cell Value | Less than | =TODAY()+30

Then press Format and choose your formatting style. I've attached an image of what the window should look like when you're finished.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

Conditional Formatting.PNG

RE: Conditional Formatting for Highlighting Dates

Hi Gary,

That worked really well. Off the back of that just a couple of other things... :-)

I already have some conditional formatting in place which seems to have been overwritten by the new conditioning, can you not have multiple conditioning rules on cells?

Also, how might I get the above to turn a whole row into a colour if the condition is met within that one cell and not just the cell?

Thanks,

Sorry can of worms!

RE: Conditional Formatting for Highlighting Dates

Hi Lisa

Thanks for your reply. All possible, with differing degrees of difficulty!

For your first question, you can have theoretically unlimited rules on a single cell / selection. Your latest rule is coming last and overriding the others. To edit this, select the affected cells, go to Home > Conditional Formatting > Manage rules. Your rules will be listed here. Click the up / down arrows to move the rules up and down in order of execution, and pay attention to the Stop if true option, will will halt any further conditional formatting rules being applies.

For the second question, you can do it but you'll have to be a bit more precise in your rules. The method I listed earlier will _not_ work for this. Select all the cells to be affected by the rule and go back to New rule and select Use a formula to determine which cells to format.

Assuming your first date is in cell A1, try the following formula:

=$A1<TODAY()+30

Then choose your formatting as before. The dollar signs in that reference are *very* important. Do change your column / row to the first occurrence of the date in your table.

Give it a go and let me know how you get on.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 24 Jun 2014: Automatically marked as resolved.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Display Formulas Instead of Results in Excel 2010

By pressing Ctrl ~ once, Excel will display formulas rather than the results of the formulas. Press it again, and the results will appear again.

A much quicker and simpler way of displaying your formulas!

View all Excel hints and tips


Server loaded in 0.06 secs.