Checking if a certain day falls in a range

[Guest]

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 !!

 

[Guest]

Hi

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

Andy.

 

[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.

 

[Guest]

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 **

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

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 !!

 

[Ian]

Glad it works for you and thanks for the feedback.

--
Ian
--

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 **

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

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 !!

 

[daddylonglegs]

Another way....

=MATCH(AK1,A2:E2,0

 

[Ian]

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