Condional Formatting within a list of dates

John Smucker

New Member
Joined
Jul 22, 2022
Messages
32
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jan 13, 2021
Messages
854
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,498
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top