Date Range Conditional Formatting
 

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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

Use conditional formatting.

Tyro

"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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.
--
when u change the way u look @ things, the things u look at change.


"Lynn" wrote:

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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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


"sahafi" wrote:

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.
--
when u change the way u look @ things, the things u look at change.


"Lynn" wrote:

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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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

"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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

"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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,C1B1))

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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


"T. Valko" wrote:

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,C1B1))

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

Hmmm...

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

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


--
Biff
Microsoft Excel MVP


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


"T. Valko" wrote:

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,C1B1))

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

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


"T. Valko" wrote:

Hmmm...

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

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


--
Biff
Microsoft Excel MVP


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


"T. Valko" wrote:

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,C1B1))

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP

Date Range Conditional Formatting
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
I figured it out! I had an extra set of parenthesis. Thank you so much
for
your help!
--
-Lynn F. PMP


"T. Valko" wrote:

Hmmm...

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

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


--
Biff
Microsoft Excel MVP


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


"T. Valko" wrote:

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,C1B1))

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.


--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
How does the COUNT function apply here?
--
-Lynn F. PMP


"T. Valko" wrote:

Select cell C1
Goto FormatConditional 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,C1B1))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Lynn" wrote in message
...
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
--
-Lynn F. PMP