May 13 2022 04:58 AM - edited May 13 2022 04:59 AM
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 |
May 13 2022 05:33 AM
@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?
May 13 2022 05:42 AM
Use conditional formatting to highlight information
=Z1=TODAY()
=WEEKNUM(A1,1)
May 13 2022 05:58 AM
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).
May 13 2022 02:33 PM - edited May 13 2022 02:49 PM
SolutionWith 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 :)
May 14 2022 05:26 AM
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))
May 15 2022 03:08 AM
May 13 2022 02:33 PM - edited May 13 2022 02:49 PM
SolutionWith 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 :)