Highlight row if column "D" contains year < 1/1/2023, organized subtotals...

[cweaver]

Hi all,

I'm working with an Excel that I've organized into subtotals (thank you to Mr Excel for that!). I need a formula for doing the following:

1. I'd like to highlight the row (customer name) if:

- Column "D" (invoice date) for that customer contains a year less than 1/1/2023,
- and the customer should not contain any dates in 2023.

Here's some sample data:

 

[cweaver]

FARMER BOYS*** Total
FARMER BOYS***	159M	871393	3/28/23
FARMER BOYS***	148M	844151	7/5/22
FARMER BOYS***	159M	869516	2/15/23
FARMER BOYS***	148M	833735	7/19/21
FARMER BOYS***	141M	839597	11/4/21
FARMER BOYS***	159M	842808	8/29/22
FARMER BOYS***	147M	848579	4/5/22
FARMER BOYS***	140M	842817	12/22/21
FARMER BOYS***	140M	856014	2/9/22
FARMER BOYS***	159M	871394	3/28/23
FARMER BOYS***	159M	860876	8/10/22
FARMER BOYS***	159M	869517	2/15/23
FARMER BOYS***	162M	879475	6/6/23
FARMER BOYS***	140M	835470	12/4/21
FARMER BOYS***	140M	856015	2/9/22
FARMER BOYS***	157M	862488	8/18/22
SENIOR LIVING ALHAMBRA VILLAS*** Total
SENIOR LIVING ALHAMBRA VILLAS***	10M	857764	4/4/22
SENIOR LIVING ALHAMBRA VILLAS***	10M	857765	4/4/22
SENIOR LIVING ALHAMBRA VILLAS***	10M	844302	1/3/22
SENIOR LIVING ALHAMBRA VILLAS***	10M	856051	2/11/22
PLEASANT VALLEY CORPORATION*** Total
PLEASANT VALLEY CORPORATION***	94M	865158	5/22/23
PLEASANT VALLEY CORPORATION***	94M	865153	5/22/23
PLEASANT VALLEY CORPORATION***	94M	865157	5/8/23
PLEASANT VALLEY CORPORATION***	93M	875526	5/11/23
PLEASANT VALLEY CORPORATION***	90M	877753	5/15/23
PLEASANT VALLEY CORPORATION***	90M	877760	5/17/23
PLEASANT VALLEY CORPORATION***	94M	865155	5/8/23
PLEASANT VALLEY CORPORATION***	13M	887024	1/30/23
PLEASANT VALLEY CORPORATION***	01M	835492	2/4/22
PLEASANT VALLEY CORPORATION***	01M	835493	2/3/22
PLEASANT VALLEY CORPORATION***	01M	843801	2/3/22
PLEASANT VALLEY CORPORATION***	01M	843802	2/3/22
PLEASANT VALLEY CORPORATION***	01M	843803	2/3/22
PLEASANT VALLEY CORPORATION***	01M	843804	2/3/22
PTI MANAGEMENT*** Total
PTI MANAGEMENT***	38M	843964	8/6/22
PTI MANAGEMENT***	38M	776350	5/1/18
PTI MANAGEMENT***	38M	776351	5/1/18
PTI MANAGEMENT***	38M	776352	5/1/18
PTI MANAGEMENT***	38M	776353	5/1/18
PTI MANAGEMENT***	38M	776354	5/1/18
PTI MANAGEMENT***	38M	776355	5/1/18
PTI MANAGEMENT***	38M	843938	8/6/22
PTI MANAGEMENT***	38M	843951	8/6/22
PTI MANAGEMENT***	38M	843952	8/6/22
PTI MANAGEMENT***	38M	843963	8/6/22
PTI MANAGEMENT***	38M	777309	6/15/18
PTI MANAGEMENT***	38M	777310	6/15/18
PTI MANAGEMENT***	38M	777311	6/15/18
PTI MANAGEMENT***	38M	777312	6/15/18
PTI MANAGEMENT***	38M	777313	6/15/18
PTI MANAGEMENT***	38M	777817	6/15/18

 

[Joe4]

Select the range from cell A1 to the last row with data in column D, then go to Conditional Formatting, enter this Conditional Formatting formula, and select your desired color:

=AND($D1<>"",YEAR($D1)<2023,COUNTIFS($A:$A,$A1,$D:$D,">="&DATEVALUE("1/1/2023"))=0)

Here is what the first 25 rows of your example look like with this:

 

[cweaver]

Thanks for the quick reply...which option under conditional formatting do I choose?

 

[Joe4]

You need to click "New Rule" (as you are creating a new rule!).
Then, after that, select the "Use a formula to determine which cells to format" option.
That is where you enter in the formula I gave you, and then select your desired formatting option/color.

 

[cweaver]

For some reason, it doesn't seem to work...

 

[Joe4]

That does not look like the formula I gave.
What was the exact range you selected before you pasted in the formula I gave you?

 

[cweaver]

This is the formula listed above...

=AND($D1<>"",YEAR($D1)<2023,COUNTIFS($A:$A,$A1,$D:$D,">="&DATEVALUE("1/1/2023"))=0)

A1 through D9002

 

[Joe4]

Please answer the question I asked, this is critical to debugging your issue:

What was the exact range you selected before you pasted in the formula I gave you?

When writing a Conditional Formatting formula for a multi-cell range, you need to write the formula as it pertains to the very first cell in your selected range.
As long as you use the absolute/relative range references correctly, Excel will automatically adjust it for all the other cells correctly.

As I said, I wrote the formula as it pertains to cell A1.
If cell A1 is NOT the first cell in the range you selected before entering my formula, it will not work properly - all the range references will be off.
If you are starting in some other cell, I need to know what it is so we can adjust the formula appropriately.

So unless you are comfortable trying to modify that yourself, you will need to provide me with that information.

 

[cweaver]

Sorry about that it's actually

A2 through D9002 That's the top of the Excel...