COUNTIFS issues with text and dates

[sirrobocop]

I am trying to count items in a database exported to excel. Everything is working except the portion of the cell codes shown below. I can't figure out why the IF statement recognizes the items in the comparison and correctly gauges "true", but COUNTIF returns a zero for the same formula. It seems that a true result would result in an increment. I presume the database is in text format, since the numbers are not standard Excel format, and are justified left, so I tried a few different approaches, shown, but none seem to count correctly. I must be missing something simple in syntax, or missing a function that allows a comparison of like-basis, but I'm stumped.

 

[etaf]

i suspect the dates are just text and so > also works on text try putting just a and b into cells and then do a > or <
c2 is probably a real date and so a number
format to general and you will see

so for count to work - then it would need to be changed to dates

Book5
	G	H	I
1
2	c		TRUE
3	b		FALSE
4
Sheet2

Cell Formulas
Range	Formula
I2	I2	=G2>G3
I3	I3	=G3>G2

 

[shift-del]

Hi

Remove the "(PDT)" from your data.

 

[sirrobocop]

Hi

Remove the "(PDT)" from your data.

Really didn't want to go to that as an option. I'd have to write a macro to do that, which I can do. I was hoping to drop in the real data, which contains 1000+ lines onto a page, and have the automation display stats and a chart of the counts without the extra steps. My research indicates COUNTIF should work with text comparisons, but I must be missing the correct function or method to create the match, as you can see in my attempts.

I'm going to strip the (PDT) from the data as suggested and see if it works that way. If no one offers a more elegant solution for the comparison in the formula, I'll write the code to strip it from all data after it's dropped in.

 

[sirrobocop]

See attachment (sorry I can't load the worksheet) We have established that COUNTIF will work for dates by altering the input data to date format, but not why the text versions can't be compared in the same manner. If someone reads this and knows why, please offer up your reasoning (especially if you have a solution to that approach). For now I'm trying a macro approach to alter the data instead of comparing the strings.

However - I'm asking this just for the sake of discussion - see the last approach in the attachment - I stripped the (PDT) on one data entry as suggested. Excel immediately altered the data into the default date format that is currently set, and immediately updated both the COUNTIF(DATE) version AND the COUNTIF(TEXT(of the date)) versions to count 1 occurrence correctly - but notice, not the text version with the wildcard on the end (the last approach). It's not important to fixing my cause, but I can't decide why it isn't also registering a count of one, since theoretically it's the same as the approach immediately prior. Is the wildcard installed wrong here?

 

[etaf]

dates are numbers starting from 1/1/1900 - so today 10 sept 22 is 44814
so when you say > greater or < less than , then that is working on numbers and can work
BUT if TEXT , then it does not work the same as numbers

Neg or Pos -ETAF.xlsx
	A	B	C	D	E
1	real	Text		real	text
2	1/3/22	1/3/22		1/1/22	01/01/22
3	5	0		1/2/22	02/01/22
4				1/3/22	03/01/22
5				1/4/22	04/01/22
6				1/5/22	05/01/22
7				1/6/22	06/01/22
8				1/7/22	07/01/22
9
Sheet2

Cell Formulas
Range	Formula
A3:B3	A3	=COUNTIF(D2:D8,">="&A2)
E2:E8	E2	=TEXT(D2,"dd/mm/yy")

 

[sirrobocop]

The date in B2 is a date number, not text. At least from what I'm seeing. This would account for the 0 in B3.

 

[etaf]

yes, thats what XL2BB will do when copied into your spreadsheet - BUT its not , it was text

so now i have tried to use the formula , TEXT() so when copied in - it will remain as text

and formatted all to general - so you can see where the date number is

Book3-ETAF.xlsx
	A	B	C	D	E
1	real	Text		real	text
2	44564	03/01/22		44562	01/01/22
3	5	0		44563	02/01/22
4				44564	03/01/22
5				44565	04/01/22
6				44566	05/01/22
7				44567	06/01/22
8				44568	07/01/22
Sheet2

Cell Formulas
Range	Formula
B2,E2:E8	B2	=TEXT(A2,"dd/mm/yy")
A3:B3	A3	=COUNTIF(D2:D8,">="&A2)

 

[sirrobocop]

No, I checked, it's a number.

However, I have figured out the problem, I think:

=COUNTIF(C4:C6,">"&TEXT(C2,"dd-mmm-yyyy hh:mm AM/PM"&"*")) <- The text string at the end is a NUMBER FORMAT, not an ordinary string. The wildcard can't be used, Excel thinks it's just an asterisk. However, if I change the line to this:

">="&TEXT($C$2,"dd-mmm-yyyy hh:mm AM/PM (PDT)")) and try the comparison, Excel counts it correctly.

Thanks for helping me look at it.

sirrobocop