Checking if a certain day falls in a range

J

Jaydubs

Dear Excellent users.

I have a day [=TODAY()] in field AK1
In A1 I have the number of the week.
In fields A2 till E2 I have the five days of the week noted in MM-DD

Now I want to use conditional format for the number of the week. I want to
check if the day of AK1 falls in the range of A2 till E2. If such is the
case, I want the number of the week highlighted.

Can someone help me??

Thank you !!
 
G

Guest

Hi

With A1 selected, go to Format/Conditional Formatting use Formula Is and
then:
=COUNTIF($A$2:$E$2,AK1)>0

Andy.
 
I

Ian

Assuming the data in A2:E2 is dates fromatted as MM-DD then select A1 asn
use conditional formatting

Formula is =AND($A$2<=TODAY(),$E$2>=TODAY())

unless you need TODAY() in AK1 for something else, you can do away with it.
 
J

Jaydubs

Hello Ian,

Thank you very much for your answer. It works for me. I do need the AK1
field for other comparisons, so I will compare to AK1, instead of TODAY() in
the conditional format.
--
** Fool on the hill **


Ian said:
Assuming the data in A2:E2 is dates fromatted as MM-DD then select A1 asn
use conditional formatting

Formula is =AND($A$2<=TODAY(),$E$2>=TODAY())

unless you need TODAY() in AK1 for something else, you can do away with it.

--
Ian
--
Jaydubs said:
Dear Excellent users.

I have a day [=TODAY()] in field AK1
In A1 I have the number of the week.
In fields A2 till E2 I have the five days of the week noted in MM-DD

Now I want to use conditional format for the number of the week. I want to
check if the day of AK1 falls in the range of A2 till E2. If such is the
case, I want the number of the week highlighted.

Can someone help me??

Thank you !!
Click to expand...
Click to expand...
 
I

Ian

Glad it works for you and thanks for the feedback.

--
Ian
--
Jaydubs said:
Hello Ian,

Thank you very much for your answer. It works for me. I do need the AK1
field for other comparisons, so I will compare to AK1, instead of TODAY()
in
the conditional format.
--
** Fool on the hill **


Ian said:
Assuming the data in A2:E2 is dates fromatted as MM-DD then select A1 asn
use conditional formatting

Formula is =AND($A$2<=TODAY(),$E$2>=TODAY())

unless you need TODAY() in AK1 for something else, you can do away with
it.

--
Ian
--
Jaydubs said:
Dear Excellent users.

I have a day [=TODAY()] in field AK1
In A1 I have the number of the week.
In fields A2 till E2 I have the five days of the week noted in MM-DD

Now I want to use conditional format for the number of the week. I want
to
check if the day of AK1 falls in the range of A2 till E2. If such is
the
case, I want the number of the week highlighted.

Can someone help me??

Thank you !!
Click to expand...
Click to expand...
Click to expand...
 
I

Ian

That's the good (and frustrating) thing about Excel. For any given problem
there is normally more than one solution.
 
Top