Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Conditional Formatting Based on Date Proximity.
Written by Allen Wyatt (last updated July 22, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003
Richard wondered if it was possible, using conditional formatting, to change the color of a cell. For his purposes he wanted a cell to be red if it contains today's date, to be yellow if it contains a date within a week of today, and to be green if it contains a date within two weeks.
You can achieve this type of conditional formatting if you apply a formula. For instance, let's assume that you want to apply the conditional formatting to cell A1. Just follow these steps:
Figure 1. The Conditional Formatting dialog box.
Figure 2. The Patterns tab of the Format Cells dialog box.
One important thing to bear in mind with conditional formatting is that criteria are evaluated in the order in which they appear. Once a criteria has been met, then the formatting is applied and other criteria are not tested. It is therefore important to set out the tests in the correct order. If, in the example above, the criteria had been entered in the reverse order, i.e. test for 14 days, then 7 and then 0, it would have only applied the 14 days format even if the date entered was today. In other words, if the date is today then all three of the tests would have been met so you have to be careful of the order in order to get the result you need.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2664) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Conditional Formatting Based on Date Proximity.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you want to apply a conditional format to data imported into Excel from Access, you may run into some difficulties ...
Discover MoreConditional formatting can be used to draw attention to all sorts of data based upon the criteria you specify. Here's how ...
Discover MoreConditional formatting is a great tool for changing how your data looks based on the data itself. Excel won't allow you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-01-04 16:02:27
Chris
This is good to know, but I am wanting to know how in a Job Schedule with a calendar to make the day boxes to shade to correlate with the start and finish dates entered for that row...?
2022-05-03 14:21:58
Erin
I have the exact same query as Lee
What about when I don't want to the data to be determined on TODAY's date.
For example,
Column A - Date of Initial Contact
Column B - Date of Final Contact
I want to use conditional formatting to show:
Green - if communication was completed in 4 days or less
Yellow - if communication was completed in 5-7 days
Red - if it took 10 or more days to complete communication.
2021-06-16 09:20:15
Michael
Emnauel Popa - you did not follow what Alan said in the comments below the instructions:
"One important thing to bear in mind with conditional formatting is that criteria are evaluated in the order in which they appear. Once a criteria has been met, then the formatting is applied and other criteria are not tested. It is therefore important to set out the tests in the correct order."
Go back into Conditional Formatting and look for the up and down arrows on the right side - click on each of the conditions and move each of them into the correct order.
1st condition has to be on top of list - to be evaluated FIRST, same for 2nd into 2nd spot etc etc.
Otherwise last will be evaluated first and the others depending on conditions might be ignored.
2021-05-20 04:23:59
Emnauel Popa
This doesn't work, I have tried it and the cell goes only in green, it follows (randomly) only one of the rules.
ANY SUGGESTIONS?
2021-04-13 07:06:45
Bob
G'Day,
I am trying to highlight cells after 17:00 and before 08:00 in order to track after hours work.
Can anyone help.
Cheers,
2020-09-19 19:02:14
John
Hello all!
I am attempting to make a formula for adding certain amounts of days to a date based off of a color scheme.
So if B2 is my accepted date( say 09/19/2020, and the individual stays for 7 days (grey, noted in C2) then I want to add 7 days to B2 date and show it in D2.
So the result would look like:
B2: 09/19/2020
C2: grey box, 7
D2: 09/26/2020
I also have several other days I’d like to add, but I just can’t figure out the formula I’d need
2020-07-02 09:37:27
English Speaker
FYI, 'criteria' is plural. The singular form is 'criterion'. So saying 'a criteria' as you do is nonsense.
2020-01-29 17:58:55
Jordan T
I am trying to highlight dates if they are not within the same calendar year as "NOW" or "TODAY." The proximity in days to the current date does not matter. Can anyone help me?
2019-02-01 10:40:12
Ced W
This tip helped intially but what about formatting a cell based on another cell ...? Like, if the due date in cell A1 is Jan1 but the date completed in cell A2 Jan2 color it red for being past due or green if completed on time ...?
2019-01-14 14:00:42
Archie
Can anyone help me, in conditional formating
Column A and column B
I wanted to condition or put color in column B if B bigger than A
Any one
2018-10-30 21:29:06
Dan Dresner
In a table, I need my entries to each evaluate as whole numbers in order to create a CRC total, in case some of the entries are frax.
2018-05-06 06:43:34
Andrew Ford
Hi,
So I have followed your explanation, with adjustment to my requirements. However, no conditional formatting is being applied.
I have a date, let's say 06/09/2018, they do vary.
I want to format backwards on a 28-day rule, so my rules entered are:
Cell Value equal to =TODAY() -84, format RED, this identifies anything entering 3 months
Cell Value between =TODAY() -84 and =TODAY()-112, format Yellow, this identifies anything between 3 / 4 months
Cell Value between =TODAY() -112 and =TODAY()-140, format Yellow, this identifies anything between 4/ 5 months
Where am I going wrong, I just need to identify dates when they approach a month period.
2018-02-20 11:21:55
Lee
What about when I don't want to the data to be determined on TODAY's date.
For example,
Column A - Date of Initial Contact
Column B - Date of Final Contact
I want to use conditional formatting to show:
Green - if communication was completed in 4 days or less
Yellow - if communication was completed in 5-7 days
Red - if it took 10 or more days to complete communication.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments