FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
April 7, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'Two Easy Methods to Extract Unique Items From a List'. You can read all about it here.


Quote of the Day
Every week I receive emails from readers who say they love the quotes in my newsletter. Often a particular quote will 'strike a chord' with them. I find that reading quotes breaks me out of my routine for a few moments and prompts me to reconsider some of my own limiting beliefs. I typically share quotes on topics such as persistence, success, gratitude, goals, knowledge and sharing.

Today I am including one of my favourite quotes by Mark Twin. So if you have been putting off some of your life goals, maybe this quote will speak to you as well.

H
ave a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


If you missed my last newsletter, you can click here to view it online.

 
TheExcelAddict.com Quote of the Day

"Twenty years from now you will be more disappointed
by the things you didn't do than by the ones you did do.
So throw off the bowlines. Sail away from the safe harbour.
Catch the trade winds in your sails.
Explore. Dream. Discover."

-- Mark Twain --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Highlight Upcoming Dates in Real Time

2016-Apr-11 Karen M.
I have already put this tip to good use!  Thank you for the great tip.

I am often asked if it is possible to have upcoming dates highlighted based on the current date
in 'real time'.

The answer is Yes and the solution can be found in Excel's Conditional Formatting.

Let's look at this example for Excel Cable TV. Lately, some of their customers have been canceling their cable TV service and switching to NetFlix and other online streaming services. The service techs are having trouble keeping track of projects, so their boss wants them to instantly be able to see which projects are due in the next week or two.

Highlight Upcoming Due Dates Spreadsheet in Microsoft Excel 2007 2010 2013 2016 365
She's asked to have due dates that are within 7 days from the current date to be highlighted in red and dates that are between 8 and 14 days from the current date, she wants highlighted in yellow.

We can use Excel's Conditional Formatting to do this. However, since there are multiple criteria, we will be doing this in multiple steps. Let's start...

DOWNLOAD PRACTICE FILE HERE

Highlight dates that are within 7 days of today

1) Select all of the date cells where you want to apply this formatting;

2) From the Home tab, click Conditional Formatting, then click New Rule... The New Formatting Rule dialog appears;

Conditional Formatting New Rule Upcoming Dates in Microsoft Excel 2007 2010 2013 2016 365

3) In the 'Select a Rule Type' section, select 'Format only cells that contain';

4) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than or equal to' from the second dropdown and type the formula =TODAY()+7 in the third field. TODAY() is an Excel function that returns the current date (in this example, the current date is April 7). So, the formula TODAY()+7 means '7 days from now'. If the Conditional Formatting rule 'Cell value is less than or equal to TODAY()+7' evaluates to True, formatting will be applied to that cell;


Conditional Formatting New Rule Upcoming Date 7 Days in Microsoft Excel 2007 2010 2013 2016 365
5) The next step is to choose the format to apply to cells that meet this condition. To do this, click on the Format... button;

6) In the Format Cells dialog, click the Fill tab and choose a background color. If you use a dark color (e.g. red) for the background, you may prefer to select a lighter font color (e.g. white) from Font tab;

7) Click OK to close the Format Cells dialog and you will see a preview of the cell formatting you selected;

Format Preview Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
8) Click OK and you will be returned to the worksheet and any dates within 7 days of the current date should be highlighted.

First Condition Formatting Applied in Microsoft Excel 2007 2010 2013 2016 365

Next, Highlight dates that are within 14 days of today

1) With the date cells still selected, click Conditional Formatting on the Home tab, then click New Rule... The New Formatting Rule dialog appears;

2) In the 'Select a Rule Type' section select 'Format only cells that contain';

3) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than or equal to' from the second dropdown and type the formula =TODAY()+14 in the third field';

4) Click on the Format... button and choose the formatting you want to apply to cells that are within 14 days of now;

5) Click OK to close the Format Cells dialog, then click OK again and you will be returned to the worksheet and all dates within 14 days of the current date should be highlighted with a yellow background.

Second Condition Formatting Applied in Microsoft Excel 2007 2010 2013 2016 365
I know! I know! The dates within 7 days aren't red anymore! So let's see why?

Changing the Order of Conditional Formatting Rules

1) With the cells still selected in the worksheet, on the Home tab, click Conditional Formatting, then select Manage Rules... The Conditional Formatting Rules Manager dialog appears.
You can now see the two rules we previously added ;

Conditional Formatting Rules Manager in Microsoft Excel 2007 2010 2013 2016 365
2) When we apply two or more rules to the same range of cells, the newer rules are always added at the top of the list in the Rules Manager. Rules are then evaluated in the order they are listed, with precedence given to the higher rules.

Since our '14 day' rule was added last, it is evaluated first. For cells where this rule
=TODAY()+14 evaluates to True, the yellow background formatting is applied. If a second or subsequent rule also evaluates to True, the formatting for that rule will be applied as well, unless there is a conflict between the two formats. In this example, the first rule sets the cell's background color to yellow and the second rule sets the cell's background to red. Because of these conflicting formats the first rule takes precedence and only the yellow background format is applied.

Obviously, there is something wrong with this. The reason is the way we have applied these rules.

There are a couple of ways to correct this. The preferred way is to add the rules in the correct order when they are being initially set up. In this example, if we had added the '14 day' rule first and then the '7 day' rule last, everything would have been fine.

Another way to correct this is to change the order of precedence by (1) selecting the '7 day' rule in the Rules Manager dialog and moving it to the top by (2) clicking the Move Up arrow, giving it precedence.

Move Rule Up Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
Now the cells are formatted correctly since the '7-day' rule is being evaluated first.

Conditional Formatting Two Rules in Microsoft Excel 2007 2010 2013 2016 365

Projects due within 7 days are highlighted in red and projects due within 14 days are highlighted in yellow. Each passing day, these rule will be evaluated based on the current date because of the TODAY() function used in the conditional formatting rules.


Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630