Conditional Format - Highlight Cell Based on Date

[Kirrash]

I would like to use conditional formatting to highlight duplicate client ID in column B - but only for the current year (column C).

I know I'm missing something and overthinking the issue. The conditional format I have tried is:

=AND($B1:$B4,">="&DATE(2024,1,1),"<="&DATE(2024,12,31),$C1:$C4>1)

I have also tried the below formula using a blank worksheet (and I did not keep the columns identical).

=COUNTIFS($C$5:$C$10,$C5,$C$5:$C$10,"<>",$B$5:$B10,"<="&DATE(2024,1,1)>1)

Thank you.

 

[DanteAmor]

How about:

varios 10oct2024.xlsm
	A	B	C
1	Work	Cli	Date
2	Van	206	05/10/2024
3	Mar	526	06/10/2024
4	Ali	678	07/10/2024
5	Sam	707	08/10/2024
6	Ali	678	07/10/2023
7	Sam	707	08/10/2024
8	Mar	526	06/10/2024
Hoja4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=COUNTIFS(B:B,B2,C:C,">=01/01/"&YEAR(TODAY()),C:C,"<=31/12/"&YEAR(TODAY()))>1	text	NO

 

[Kirrash]

Hello and thank you for showing me this! Can you upload that sample file? I created a rule for the conditional format and it is not working. Thank you

 

[DanteAmor]

Change Applies to:
=$B$2:$B$8

And try, with format mm/dd/

=COUNTIFS(B:B,B2,C:C,">=01/01/"&YEAR(TODAY()),C:C,"<=12/31/"&YEAR(TODAY()))>1

 

[Kirrash]

That is most excellent, sir, thank you. How would you make EVERY instance highlight and not just the first one?

 

[Kirrash]

Actually, I see the formula is using B2 - how does that work? =countsifs with B:B as the range, and b2 as the criteria. The criteria should be any unique number that is duplicated. Thank you for your patience.

 

[DanteAmor]

How would you make EVERY instance highlight and not just the first one?

It works for all instances, if you check it out in my post #2.
Also check where you want to apply, as I put in post #4

Applies to:
=$B$2:$B$8

Cell B2 must match in applies and in the formula

Do you have other conditional formats on the sheet?

 

[Kirrash]

Thank you once again; this was was rough on me and you created a perfect solution.

 

[DanteAmor]

I see the formula is using B2 - how does that work? =countsifs with B:B as the range, and b2 as the criteria

To explain, put the following on a new sheet.

varios 10oct2024.xlsm
	A	B	C	D	E
1	Work	Cli	Date		Formula
2	Van	206	05/10/2024		FALSE
3	Mar	526	06/10/2024		TRUE
4	Ali	678	07/10/2024		FALSE
5	Sam	707	08/10/2024		TRUE
6	Ali	678	07/10/2023		FALSE
7	Sam	707	08/10/2024		TRUE
8	Mar	526	06/10/2024		TRUE
Hoja5

Cell Formulas
Range	Formula
E2:E8	E2	=COUNTIFS(B:B,B2,C:C,">="&DATE(YEAR(TODAY()),1,1),C:C,"<="&DATE(YEAR(TODAY()),12,31))>1

In cell E2, in the formula you can see B2 (without currency symbols) is the criteria for column B.
In cell E3, in the formula you can see B3 as criteria for column B and so on.

The same thing happens in the conditional format, we only put B2 once, but in Applies, we are telling it that it is going to apply from B2 to B8, that means that the formula the criteria is B2 for B:B, for cell B3 the criteria is B3 for B:B and so on.

I hope it's helpful.

 

[Kirrash]

I really appreciate you explaining this to me. Saving this for future reference.

Last question. Will I have to continually update the "Applies to" and increase the range or is there a way to make it increase as rows are added?