Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# conditional formatting based on dates

Posted on 2011-09-16
Medium Priority
221 Views
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.

0
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
• 4
• 3
• 3

LVL 50

Expert Comment

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 93

Expert Comment

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 93

Expert Comment

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

LVL 50

Accepted Solution

barry houdini earned 2000 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

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

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 93

Expert Comment

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

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

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

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

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦