# COUNTIF with Time values...

#### Aitch

##### Board Regular
I have column A with times like this:

11:00
11:30
12:00
14:00
14:30
15:00

I'm trying to count how many times are in the afternoon like this:

=COUNTIF(A1:A , ">&TIME(12,0,0)" )

But it counts all the cells instead of just those later than 12:00

What am I doing wrong?

Thanks for any help!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### JoeMo

##### MrExcel MVP
=COUNTIF(A1:A6,">12:00")

#### Aitch

##### Board Regular
No luck!

The times for column A come from a text string on another sheet - I use RIGHT to cut away the last five digits which contain the time:

=RIGHT(Meetings!C1,5)

I've made sure that the column is formatted HH:MM but it still doesn't work...

#### Noxqss38242

##### Board Regular
So, you've used a formula to get the time and now the time doesn't act like time.

Try using this on your extracted time in cell B1:

=TIME(LEFT(A1,FIND(":",A1)-1)*1,RIGHT(A1,2)*1,0)+INT(LEFT(A1,FIND(":",A1)-1)*1/24)

For counting now here is a formula to count how many meetings before 12:00

=COUNTIFS(B1:B6,">="&TIME(5,0,0),B1:B6,"<"&TIME(11,59,0))

For counting meetings after 12:00

=COUNTIF(B1:B6,">="&TIME(12,0,0)

#### Noxqss38242

##### Board Regular

So, you've used a formula to get the time and now the time doesn't act like time.

Try using this on your extracted time in cell B1:

=TIME(LEFT(A1,FIND(":",A1)-1)*1,RIGHT(A1,2)*1,0)+INT(LEFT(A1,FIND(":",A1)-1)*1/24)

For counting now here is a formula to count how many meetings before 12:00

=COUNTIFS(B1:B6,">="&TIME(5,0,0),B1:B6,"<"&TIME(11,59,0))

For counting meetings after 12:00

=COUNTIF(B1:B6,">="&TIME(12,0,0)

=COUNTIFS(B1:B6,">="&TIME(5,0,0),B1:B6,"<"&TIME(12,0,0))
=COUNTIF(B1:B6">="&TIME(12,0,0))

First formula missed time of 11:59
Second formula forgot the other parenthesis...ha!

Pretty sure you can take it from here...

#### Aitch

##### Board Regular
Awesome, thanks man! True, I hate it when time doesn't act like time lol!

I've tried to simplify - this seems to work, but could it potentially cause problems?

=TIME (LEFT (RIGHT (Meetings!C1 , 5) , 2) , RIGHT (Meetings!C1 , 2) , 0)

#### Noxqss38242

##### Board Regular

I currently do not see any issue with that.

I believe your original formula is off?

This works for me:

=COUNTIF(A1:A6,">="&TIME(12,0,0))

#### Aitch

##### Board Regular
Yeah it is, but your TIME formula seems to make =COUNTIF(A1:A , ">12:00") work fine now!

#### Noxqss38242

##### Board Regular
Ah ha! Just in time for Thanksgiving! Hahaha....glad something worked!

Replies
1
Views
54
Replies
4
Views
179
Replies
4
Views
95
Replies
2
Views
67
Replies
3
Views
98

1,127,585
Messages
5,625,656
Members
416,124
Latest member
DeMoNloK

### 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