SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3370017%22%20slang%3D%22en-US%22%3ENeed%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370017%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20a%20set%20of%20dates%20(7%20days)%20set%20termed%20as%20Week-1%2C2%2C%26amp%3B%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20highlight%20the%20cell%20range%20where%20today%20date%20has%20fallen.%20Can%20you%20please%20help%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221122%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22374%22%3EWEEK%201%3C%2FTD%3E%3CTD%20width%3D%22374%22%3EWEEK%202%3C%2FTD%3E%3CTD%20width%3D%22374%22%3EWEEK%203%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18-Dec-21%3C%2FTD%3E%3CTD%3E24-Dec-21%3C%2FTD%3E%3CTD%3E14-May-22%3C%2FTD%3E%3CTD%3E20-May-22%3C%2FTD%3E%3CTD%3E09-Jul-22%3C%2FTD%3E%3CTD%3E15-Jul-22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E25-Dec-21%3C%2FTD%3E%3CTD%3E31-Dec-21%3C%2FTD%3E%3CTD%3E01-Jan-22%3C%2FTD%3E%3CTD%3E07-Jan-22%3C%2FTD%3E%3CTD%3E21-May-22%3C%2FTD%3E%3CTD%3E27-May-22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01-Jan-22%3C%2FTD%3E%3CTD%3E07-Jan-22%3C%2FTD%3E%3CTD%3E04-Jun-22%3C%2FTD%3E%3CTD%3E10-Jun-22%3C%2FTD%3E%3CTD%3E06-Aug-22%3C%2FTD%3E%3CTD%3E12-Aug-22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E08-Jan-22%3C%2FTD%3E%3CTD%3E14-Jan-22%3C%2FTD%3E%3CTD%3E19-Mar-22%3C%2FTD%3E%3CTD%3E25-Mar-22%3C%2FTD%3E%3CTD%3E18-Jun-22%3C%2FTD%3E%3CTD%3E24-Jun-22%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3370017%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370206%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%3CEM%3E%26nbsp%3B%22Where%20today's%20date%20has%20fallen%22.%3C%2FEM%3E%20What%20does%20that%20mean%3F%20What%20would%20you%20like%20to%20highlight%20in%20the%20example%20data%20set%20with%20today%20being%20the%2013th%20of%20May%202022%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370260%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EAs%20has%20already%20been%20said%2C%20your%20intention%20is%20not%20entirely%20clear%20from%20the%20text.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3ENevertheless%2C%20I%20am%20sending%20you%20these%20links%2C%20maybe%20they%20will%20help%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E...if%20not%2C%20please%20describe%20in%20more%20detail%20%3A).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20conditional%20formatting%20to%20highlight%20information%3C%2FA%3E%3C%2FP%3E%3CP%3E%3DZ1%3DTODAY()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-weeknum-function%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20WEEKNUM%20Function%3C%2FA%3E%3C%2FP%3E%3CP%3E%3DWEEKNUM(A1%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370329%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20See%20below%20table%2C%20today%20date%2013-05-2022%2C%20which%20is%20fallen%20between%2010-05-2022%20to%2016-05-2022%20range%20in%20week%202%20(Circled).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20is%20a%20conditional%20formatting%20(Green)%2C%20whenever%20the%20date%20fallen%20between%20range%20of%20dates.%20(Weeks).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sameer_Kuppanath_Sultan_1-1652446537283.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371453iC30C213B0A2C731B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sameer_Kuppanath_Sultan_1-1652446537283.png%22%20alt%3D%22Sameer_Kuppanath_Sultan_1-1652446537283.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3373450%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3373450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EWith%20conditional%20formatting%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3D((B2%26lt%3B%24A2)*(C2%26gt%3B%24A2)*(MOD(COLUMN(B2)%2C2)%3D0))%2B((B2%26gt%3B%24A2)*(A2%26lt%3B%24A2)*(MOD(COLUMN(B2)%2C2)%3D1))%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EBefore%20that%2C%20highlight%20B2%3AG7%20and%20then%20Formula%20in%20Conditional%20formatting%20and%20select%20color.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E...maybe%20it%20will%20help%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375190%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3Ehere%20is%20a%20suggested%20solution%20with%20SUMPRODUCT...I%20think%20it%20fits%20better%20%3A)).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((%24B2%3A%24G2%26lt%3B%24A2)*(%24C2%3A%24H2%26gt%3B%24A2)*(MOD(COLUMN(%24B2%3A%24G2)%2C2)%3D0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376368%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20Highlight%20the%20range%20of%20Dates%20(Week)%20where%20today%20date%20is%20fallen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376368%22%20slang%3D%22en-US%22%3EThanks%20-%20It%20works.%3C%2FLINGO-BODY%3E
Frequent Contributor

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 1WEEK 2WEEK 3
18-Dec-2124-Dec-2114-May-2220-May-2209-Jul-2215-Jul-22
25-Dec-2131-Dec-2101-Jan-2207-Jan-2221-May-2227-May-22
01-Jan-2207-Jan-2204-Jun-2210-Jun-2206-Aug-2212-Aug-22
08-Jan-2214-Jan-2219-Mar-2225-Mar-2218-Jun-2224-Jun-22
6 Replies

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

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

 

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

 

Sameer_Kuppanath_Sultan_1-1652446537283.png

 

best response confirmed by Sameer_Kuppanath_Sultan (Frequent Contributor)
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

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