Date Range Conditional Formatting

[Lynn]

Help! I can't get the following to work & I haven't been able to find the
solution in this discussion group.
A1 = 1/16/08
B1 = 2/6/08
I want C1 to be green if it contains a date between the dates in A1 & B1
inclusive.
I want C1 to be red if it contains a date that is not between the dates in
A1 & B1

 

[T. Valko]

Select cell C1
Goto Format>Conditional Formatting
Condition 1
Formula Is: =AND(COUNT(A1,B1)=2,C1>=A1,C1<=B1)
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2
Formula Is: =AND(COUNT(A1,B1)=2,OR(C1<A1,C1>B1))
Click the Format button
Select the desired style(s)
OK out

 

[Tyro]

Use conditional formatting.

Tyro

 

[sahafi]

If I understood your problem correctly, highlight C1 and setup the CF by
selecting 'between' then the two ranges A1 and B1 then format as green. Click
Add and this time select 'Not between' from the dropdown list then click on
both A1 and B1 consecutively then highlight Red.

H.T.H.

 

[Lynn]

How does the COUNT function apply here?

 

[Lynn]

Tried 'between' and 'not between' - that seemed the logical choice but it
didn't work. Is there a problem using them with date ranges?

 

[T. Valko]

It's making sure that there are 2 dates entered in both A1 and B1.

Depending on your use you may not need it in there but it won't hurt
anything.

For example, with condition 1, if A1 is empty but both B1 and C1 contain
certain dates the format will be applied.

With condition 2, if B1 is empty but both A1 and C1 conatin certain dates
the format will be applied.

I don't think you would want any format applied unless you have all 3 dates
entered.

 

[Tyro]

Excel maintains dates as numbers. The COUNT function is verifying that there
are 2 numbers, one in A1 and one in B1 and if there are, it compares C1 to
see if it is in range of the numbers (dates) in A1 and B1.

Tyro

 

[Tyro]

Perhaps a good read of a book by an author such as John Walkenbach
http://j-walk.com/ss on Excel on how Excel maintains dates and times and
other things, $40 would be informative. It's quite easy reading.

Tyro

 

[T. Valko]

I don't think you would want any format applied unless you have all 3 dates

entered.

With that in mind then we should change the formulas if you're formatting
the fill color:

Condition 1:
=AND(COUNT(A1:C1)=3,C1>=A1,C1<=B1)

Condition 2:
=AND(COUNT(A1:C1)=3,OR(C1<A1,C1>B1))

If you're formatting the font color then the original formulas will be ok.

 

[Lynn]

Unfortunately I get the following error message: "You may not use unions,
intersections, or array constants for Conditional Formatting criteria."

 

[T. Valko]

Hmmm...

That's odd! None of the formulas I suggested contain any of those.

Post the *exact* formula you tried that causes that message.

 

[Lynn]

I figured it out! I had an extra set of parenthesis. Thank you so much for
your help!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I figured it out! I had an extra set of parenthesis. Thank you so much
for
your help!