Highlight cells which contain Year

[vijay.vizzu]

Dear All,

I have a sheet, in which lot of dates are there. I want to create a CF to highlight cells which contains specific year. For example

if the cells contain 1-1-1908, 2-10-1908, 11-11-1908 (these three cells belongs to year 1908). Now if i put this year 1908 in CF, all these cells should be highlighted

There are so many dates and years

 

[Somendra Misra]

Hi Vijay.vizzu

Select the whole data where you want CF and go to new rule in CF and put this formula.

=YEAR(A1) = 1908
Just see A1 is the first cell of my range you should put the first cell of your range selected for CF.

Regards!

 

[vijay.vizzu]

thank you so much somendra misra.. It works like charm

 

[Somendra Misra]

Glad, it worked for you.

Regards!

 

[vijay.vizzu]

Sorry ... how can we use this trick in countif. like if i want to count the number of cells which are the year 1908. How can i do that

 

[Somendra Misra]

Use something like this
=SUMPRODUCT((YEAR(H884:H888)=2012)*1)

Regards!