COUNTIF using structured reference and dates

[PatRichard]

Greetings - I didn't see this in a search, but maybe I'm not searching right. I have a table with a column of dates. Some of the dates are mm/dd/yyyy, and some are just mm/yyyy. I'm trying to count the number of cells in a column in which the year is a specific value. But I'm just coming up with 0. I'm using:

=COUNTIF((MyInventory[Born On]),1985)

but also tried replacing the year with a cell reference (I'm building a table that shows quantities by year).

What am I missing here?

 

[jdellasala]

I am fully aware of that, see post#4
And your comment makes no sense in relation to what I said in reply to post#6

I guess a picture's worth a lot of words!

Book1
	A	B	C
1	Date		2
2	01/01/2022		4
3	02/2022
4	03/01/2022
5	04/2022
Sheet5

Cell Formulas
Range	Formula
C1	C1	=COUNT(A2:A5)
C2	C2	=COUNTA(A2:A5)

 

[Fluff]

I've absolutely no idea what your point is.

 

[jdellasala]

I am fully aware of that, see post#4
And your comment makes no sense in relation to what I said in reply to post#6

Guess sometimes it takes a picture.

Book1
	A	B	C
1	Date		2
2	01/01/2022		4
3	02/2022
4	03/01/2022
5	04/2022
Sheet5

Cell Formulas
Range	Formula
C1	C1	=COUNT(A2:A5)
C2	C2	=COUNTA(A2:A5)

Without any other info, can you tell which cells are date and which are text?

 

[Fluff]

I still don't see what your point is. Try using words.

 

[jdellasala]

I still don't see what your point is. Try using words.

The FORMAT of a cell can mask what it actually is. In the sample I just posted, the FORMAT of Cell A3 is yy/dddd

The FORMAT of cell is General - Right Aligned.

Why else would the COUNT function for A2:A5 return 2, and COUNTA returns 4.
The only reason the YEAR function wouldn't return the year of a date is if it isn't a date.

 

[Fluff]

The only reason the YEAR function wouldn't return the year of a date is if it isn't a date.

I know that, have you actually read post#4?.

Now please lets wait to hear back from the OP, rather than flooding the thread with posts that don't actually say anything new.

 

[PatRichard]

Make sure that all the dates are real dates & not text.

Ensured all cells are date and it made no difference.

If you're not getting a valid return from something like COUNTIF (or COUNTIFS if you have a recent version of Excel - what OS and Excel Version can be added to your Profile), then those "dates" are not actually dates. If they were, regardless of their formatting (mm/dd/yyyy or mm/yyyy) they would represent an actual date, although in the second case you wouldn't know what the date is without either changing the format or using the DAY function. You should fix the data rather than find a work-around to bad data.

"Fixing" the data isn't an option. Older records don't have a day, just mm/yyyy. Nothing I can do about that right now. Setting those to be the first of the month just to get them into the right format isn't an option, either.

Are you able to add this column to MyInventory, and then count that instead?

=YEAR([@[Born On]])

This seems to work!

Guess sometimes it takes a picture.

Book1
	A	B	C
1	Date		2
2	01/01/2022		4
3	02/2022
4	03/01/2022
5	04/2022
Sheet5

Cell Formulas
Range	Formula
C1	C1	=COUNT(A2:A5)
C2	C2	=COUNTA(A2:A5)

Without any other info, can you tell which cells are date and which are text?

They are all formatted as date cells