COUNTIF with Time values...

[Aitch]

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!

 

[JoeMo]

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

 

[Aitch]

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]

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)

Adjust the formula according to your data now.....hope that helps!

 

[Noxqss38242]

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)

Adjust the formula according to your data now.....hope that helps!

=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]

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]

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]

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

 

[Noxqss38242]

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