Countif Not Counting Left Date

[itchdog]

Hi.

I'm using a left function to get the hour from a cell that contains a date, and then using COUNTIF to get a total of how many columns match that hour:

UC.Data!M2: 9/9/14 6:13 PM
C2: =UC.Data!M2
G2: =LEFT(HOUR(C2),2)
I2: =COUNTIF(G2,">9")

G2 displays "18" in the cell (left 2 of 1800). The COUNTIF in I2 should return a 1 because 18 is greater than 9. Am I doing something wrong?

 

[Norie]

What happens if you just use HOUR(C2)?

 

[FDibbins]

As Norrie suggested, =HOUR(C2) will give you 18...IF your cell contains a real date/time combo, and not just text looking like date/time

 

[itchdog]

Well I'll be! It works that way. Is there a reason the countif didn't see the 18 with the left(hour(c2),2) function?

Thanks for the quick response.

 

[Norie]

LEFT would be converting the numeric hour to a string, which could be the problem.