Locating specific dates in a column

[datadummy]

I am trying to write a formula that will reference a column and give me a count of all cells that have the specified year in them. Example column H will various years worth of data in this format 1/01/2017 and I am attempting to get counts of each years total, so month and day are pretty much irrelevant.

 

[datadummy]

So I entered both equations and got 2 separate results neither of which were the 4999. I tried highlighting all the cells and changing the format to Date but the results didn't change. I also filtered out any blanks or other formats that appeared in the column, but there are still blank cells that are appearing in the column. Any thoughts?

 

[Joe4]

Sorry, I have been away for a while.

I tried highlighting all the cells and changing the format to Date but the results didn't change.

That is not enough to convert text entries to date entries. Changing the format only works on cells that are already entered as numbers or dates. It does nothing to text entries.
You would need to use Text to Columns to do that.

Can you please answer the questions I asked in my previous post?
What did the two formulas I posted return?

 

[datadummy]

To answer your questions regarding the formulas. The first formula {=SUM(IF(ISNUMBER('GC Card Tracking'!H24:H3670),1))} it returned 1154. The second formula =COUNTBLANK('GC Card Tracking'!H24:H3670) returned 2472. I'm some what at a loss on this and have resorted to filtering out the years and blanks and just getting a total count with the ones that are populated.

 

[Joe4]

If you add up 1154 and 2472, that returns 3626.
If you count from row 24 to row 3670, that returns 3647.
So there are 21 rows unaccounted for, presumably text entries.

Let's see if we can find the problem cells.
On your 'GC Card Tracking' sheet, pick some blank column, and place this formula on row 24 and copy down to row 3670:

=IF(OR(ISNUMBER(H24),COUNTBLANK(H24)=1),"","PROBLEM HERE!")

Then inspect the column. Any row that returns "PROBLEM HERE!" is a problem, as that indicates it is non-blank, non-numeric (and hence non-date) entry.

 

[datadummy]

If you add up 1154 and 2472, that returns 3626.
If you count from row 24 to row 3670, that returns 3647.
So there are 21 rows unaccounted for, presumably text entries.

Let's see if we can find the problem cells.
On your 'GC Card Tracking' sheet, pick some blank column, and place this formula on row 24 and copy down to row 3670:

=IF(OR(ISNUMBER(H24),COUNTBLANK(H24)=1),"","PROBLEM HERE!")

Then inspect the column. Any row that returns "PROBLEM HERE!" is a problem, as that indicates it is non-blank, non-numeric (and hence non-date) entry.

That worked! It found each cell in column H that was non-date. That was freaking cool, thanks for the help locating the problem cells this was awesome I totally appreciate the help!!!

 

[Joe4]

You are welcome!
I am glad I was able to help you out.