# Countif Not Counting Left Date

#### itchdog

##### New Member
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?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What happens if you just use HOUR(C2)?

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

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.

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

Replies
5
Views
152
Replies
3
Views
277
Replies
11
Views
256
Replies
12
Views
1K
Replies
10
Views
251

1,219,828
Messages
6,150,473
Members
450,966
Latest member
Yali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back