SOLVED

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

Frequent Contributor

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

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

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

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

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

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

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

Use conditional formatting to highlight information

=Z1=TODAY()

Excel WEEKNUM Function

=WEEKNUM(A1,1)

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

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

best response confirmed by Sameer_Kuppanath_Sultan (Frequent Contributor)
Solution

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

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

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

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

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

Thanks - It works.