Need to Highlight the range of Dates (Week) where today date is fallen

[Sameer_Kuppanath_Sultan]

Hi I have a set of dates (7 days) set termed as Week-1,2,& 3.

I want to highlight the cell range where today date has fallen. Can you please help 

WEEK 1	WEEK 2	WEEK 3
18-Dec-21	24-Dec-21	14-May-22	20-May-22	09-Jul-22	15-Jul-22
25-Dec-21	31-Dec-21	01-Jan-22	07-Jan-22	21-May-22	27-May-22
01-Jan-22	07-Jan-22	04-Jun-22	10-Jun-22	06-Aug-22	12-Aug-22
08-Jan-22	14-Jan-22	19-Mar-22	25-Mar-22	18-Jun-22	24-Jun-22

[#2]

@Sameer_Kuppanath_Sultan "Where today's date has fallen". What does that mean? What would you like to highlight in the example data set with today being the 13th of May 2022?

[#3]

@Sameer_Kuppanath_Sultan 

As has already been said, your intention is not entirely clear from the text. Nevertheless, I am sending you these links, maybe they will help you :)

...if not, please describe in more detail :).

Use conditional formatting to highlight information

=Z1=TODAY()

Excel WEEKNUM Function

=WEEKNUM(A1,1)

[#4]

@NikolinoDE 

Please See below table, today date 13-05-2022, which is fallen between 10-05-2022 to 16-05-2022 range in week 2 (Circled).

What I need is a conditional formatting (Green), whenever the date fallen between range of dates. (Weeks).

[#5 · Accepted solution]

@Sameer_Kuppanath_Sultan 

With conditional formatting

=((B2<$A2)*(C2>$A2)*(MOD(COLUMN(B2),2)=0))+((B2>$A2)*(A2<$A2)*(MOD(COLUMN(B2),2)=1))

Before that, highlight B2:G7 and then Formula in Conditional formatting and select color.

...maybe it will help :)

NikolinoDE

[#6]

@Sameer_Kuppanath_Sultan 

here is a suggested solution with SUMPRODUCT...I think it fits better :)).

=SUMPRODUCT(($B2:$G2<$A2)*($C2:$H2>$A2)*(MOD(COLUMN($B2:$G2),2)=0))

[#7]

Thanks - It works.