Conditional format with LEFT, LEN, FIND and WEEKNUM

[blbat]

=LEFT($O2,LEN($O2)-FIND("-", $O2))*1=WEEKNUM(TODAY()*1)
(this formula returns FALSE if entered in a cell, but does not work in conditional format dialog box.)

Cell O2 Contains date values entered as: 03/30/2022-05/25/2022

if the first date is in current week I want to highlight the cell with yellow.

any help would be appreciated!

 

[jtakw]

Hi,

If your cell values are Always in that Exact format (e.g. mm/dd/yyyy-mm/dd/yyyy)

Book3.xlsx
	O
1
2	03/30/2022-05/25/2022
3	04/08/2022-06/25/2022
Sheet1072

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2:O3	Expression	=WEEKNUM(LEFT(O2,10))=WEEKNUM(TODAY())	text	NO

If the format may differ, as in my sample O3 below, then use this:

Book3.xlsx
	O
1
2	03/30/2022-05/25/2022
3	3/28/2022-06/25/2022
Sheet1072

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2:O3	Expression	=WEEKNUM(LEFT(O2,FIND("-",O2)-1))=WEEKNUM(TODAY())	text	NO

 

[blbat]

Thanks!...will use the second formula, in case anyone enters dates without a preceding zero.

 

[jtakw]

You're welcome, thanks for the feedback.