• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

conditional formatting based on dates

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
Tavasan65
Asked:
Tavasan65
  • 4
  • 3
  • 3
1 Solution
 
barry houdiniCommented:
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
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
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
 
barry houdiniCommented:
>Hm, I see barry beat me by a few seconds

:)

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

regards, barry
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Patrick MatthewsCommented:
teylyn,

Good point, which I'd forgotten about.

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

Patrick
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
barry houdiniCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now