Solved

conditional formatting based on dates

Posted on 2011-09-16
10
217 Views
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.
0
Comment
Question by:Tavasan65
[X]
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
10 Comments
 
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

=AND(C1>=$Y$1,C1<=$Z$1)

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
0
 
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:

=AND(A2>=DATE(2011,7,1),A2<DATE(2011,10,1))
=AND(A2>=DATE(2011,10,1),A2<DATE(2012,1,1))
=AND(A2>=DATE(2012,1,1),A2<DATE(2012,4,1))
=AND(A2>=DATE(2012,4,1),A2<DATE(2012,7,1))

Note that the way I constructed the conditions, these formulas will accommodate a time portion.
0
 
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 :)
0
Industry Leaders: 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!

 
LVL 50

Accepted Solution

by:
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
27312995.xlsx
0
 
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
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36552443
Hello,

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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36552461
teylyn,

Good point, which I'd forgotten about.

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

Patrick
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36552553
Patrick,

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


Book3.xlsx
0
 
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

=AND(C1>=$Y$1,C1<$Z$1+1)

regards, barry
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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


0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

726 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