conditional formatting based on dates

Posted on 2011-09-16
Last Modified: 2012-05-12
I want to use conditional formating based on 4 dates.    
The dates are below
1st Timeframe:  July 1, 2011 thru September 30, 2011
2nd Timeframe: October 1, 2011 thru December 31, 2011
3rd Timeframe:  January 1, 2012 thru March 31, 2012
4th Timeframe:  April 1, 2012 thru June 30, 2012

Each timeframe will be formatted differently.

Thanks in advance.
Question by:Tavasan65
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
LVL 50

Expert Comment

by:barry houdini
ID: 36552396
The simplest way to do this would be to put those dates in a table somewhere in your worksheet, e.g. put the start dates for each ranges in Y1:Y4 and the corresponding end dates in Z1:Z4

Now if the dates you want to format are in column C, for instance then select that column and use this formula in conditional formatting


choose required format

Now add another condition with $Y$1 and $Z$1 change to $Y$2 and $Z$2 with a different format. Repeat for conditions 3 and 4

regards, barry
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36552397
Assuming the date will be entered in A2, and that that is the same cell you want formatted, create four formula-based rules using the following formulae:


Note that the way I constructed the conditions, these formulas will accommodate a time portion.
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36552401
Hm, I see barry beat me by a few seconds :)

If there is any possibility of there being a time portion in your data, please do keep that last note from my comment in mind :)
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 36552416
Here's an sample sheet showing my suggestion....

dates are randomly generated in column C press F9 to re-generate

regards, barry
LVL 50

Expert Comment

by:barry houdini
ID: 36552425
>Hm, I see barry beat me by a few seconds


you are, of course, right about the times.......

regards, barry
LVL 50
ID: 36552443

chiming in late: Don't know if that also applies to 2007, but in Excel 2010 you can easily create the conditions with

Conditional Formatting > Highlight cells rules > Between
Pick the start and the end dates, define a format and OK.

With this approach you don't need to worry about formulas. Create a rule for each of the time frames.

cheers, teylyn
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36552461

Good point, which I'd forgotten about.

Of course, if the dates might have time portions, then between won't work :)

LVL 50
ID: 36552553

not sure I understand the problem with the time portion. The attached has CF set up in the way I described, with time portion in the CF as well as time portion in the date/time values.

I entered them via the method I suggested above. Excel defaulted to 0:00 as the time portion in the dialog. I adjusted it to another time and created two rules.

It seems to be working as I expect. Inspecting the existing rules, the condition values now show in General format.

cheers, teylyn

LVL 50

Expert Comment

by:barry houdini
ID: 36552591
Hello teylyn - thanks for playing!!

I believe that Patrick is referring to dates/times on the boundary dates, so if you set 1 range to be between 1st July and 30th September and the next range to be 1st October to 31st December then any entry that is a date on 30th September, but with a time portion, will fall between those 2 ranges

It might be better to specify end dates that are 1 larger, e.g. first period 1-Jul-2011 to 1-Oct-2011 and then second period 1-Oct-2011 to 1-Jan 2012

Such an approach will accommodate times in the data as per Patrick's point, and if you have a date (only) like 1-Oct-2011 that will go into the second "bin" as required (presumably because, if the conditions are applied in order Excel actually applies the last first).

Personally I think I prefer my approach (who would have thought it!) for 2 reasons -

1) easy changeability - you can change the table of dates rather than messing with the CF
2) transparency - you can view the colours that will be applied to each date range

To accommodate times with my suggestion change formula to


regards, barry
LVL 50
ID: 36552635
Ah, I see what you mean now.

Yes, with the UI approach and not entering formulas, it would require to define

>> 1st Timeframe:  July 1, 2011 thru September 30, 2011

as "Between July 1, 2011 and October 1, 2011"

to capture September 30, 2011 3 pm, which is slightly confusing.

So, it's up to user preference: Either they need to understand how Excel stores time/date values, or they need to get their head around the AND() function and referring to cells in CF.

cheers, teylyn


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question