Conditional Formatting - Date

[justfacebook564]

Hi all,

I tried to use the Conditional Formatting function to filter out the dates that occurred before 2020, my column contains cells along the lines of 11/01/2019, 21/09/2019, 21/11/2020, 01/05/2020 etc. As usual, I followed the HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains steps, and typed in 2019, as I want to find the cells containing my keyword, that is 2019 here, and make adjustments- however, it simply did not work, the cells containing 2019 did not get highlighted automatically by the system.

May I know what went wrong?

Any help would be greatly appreciated.

 

[gaz_chops]

Dates are stored in Excel as number of days from ( i think 1/1/1900), so e.g. today is stored as 43,927, it therefore does not contain 2019

 

[gaz_chops]

If your dates are in col A then In another column type
=year(A1)
copy down & use that to highlight

 

[gaz_chops]

You could also use the formula as below

 

[COwen]

You can also format your cells as text. and the enter the following formula in conditional formatting: =Right(A1, 4)="2019". What would work best so that you do not have an extra column, Put the following formula into you conditional formatting:

=YEAR($A1)=2019

 

[COwen]

@gaz_chops beat me to it

 

[COwen]

He also uses Mac---my favorite!