COUNTIF with Time values...

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Some videos you may like

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.

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Sep 15, 2017
Messages
129
Office Version
  1. 2016
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

Board Regular
Joined
Sep 15, 2017
Messages
129
Office Version
  1. 2016

ADVERTISEMENT

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

Board Regular
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Sep 15, 2017
Messages
129
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
Jan 27, 2019
Messages
117
Office Version
  1. 2010
Platform
  1. Windows
Yeah it is, but your TIME formula seems to make =COUNTIF(A1:A , ">12:00") work fine now!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top