# Condional Formatting within a list of dates

#### John Smucker

##### New Member
I have a list of dates from 01/01/2022 through 12/31/2022 This gives me 365 dates
I dragged it down another row to count the leap year, now for the year 2022 01/01/2023 appears and i want to conditionally format this list so that any dates from the next years would not show.
I tried this formula =YEAR(B368)<>YEAR(B3) and formatted the number to custom>;;; to take away the value and it took away all my dates..
Can someone explain why this isn't working

Cell B3 is the first date in the list

Cell B368 is the last date in the list

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### JamesCanale

##### Well-known Member
It really depends on what exactly you have selected when you start the conditional formatting.

Here is the last few rows of it working (thanks - I learned about the ;;; thing). It works great.

MrExcelPlayground11.xlsx
B
35012/14/2022
35112/15/2022
35212/16/2022
35312/17/2022
35412/18/2022
35512/19/2022
35612/20/2022
35712/21/2022
35812/22/2022
35912/23/2022
36012/24/2022
36112/25/2022
36212/26/2022
36312/27/2022
36412/28/2022
36512/29/2022
36612/30/2022
36712/31/2022
368
Sheet15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:B368Expression=YEAR(B3)<>YEAR(B\$3)textNO

#### Fluff

##### MrExcel MVP, Moderator
As you have 365, rather than dragging the dates why not use a spill formula that will never return dates from next year.
Fluff.xlsm
ABC
1
2
301/01/202201/01/2020
402/01/202202/01/2020
503/01/202203/01/2020
604/01/202204/01/2020
705/01/202205/01/2020
806/01/202206/01/2020
907/01/202207/01/2020
1008/01/202208/01/2020
1109/01/202209/01/2020
1210/01/202210/01/2020
36327/12/202226/12/2020
36428/12/202227/12/2020
36529/12/202228/12/2020
36630/12/202229/12/2020
36731/12/202230/12/2020
36831/12/2020
369
Data
Cell Formulas
RangeFormula
B4:B367,C4:C368B4=B3+SEQUENCE(DATE(YEAR(B3),12,31)-B3)
Dynamic array formulas.

Replies
7
Views
152
Replies
2
Views
171
Replies
1
Views
88
Replies
9
Views
173
Replies
3
Views
163

1,186,330
Messages
5,957,251
Members
438,295
Latest member
nm005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back