conditional formatting based on dates

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

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.

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

>Hm, I see barry beat me by a few seconds

:)

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

regards, barry

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

teylyn,

Good point, which I'd forgotten about.

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

Patrick

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

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

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