Solved

conditional formatting based on dates

Posted on 2011-09-16
10
215 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now