COUNTIF function issue

thesanealien

New Member
Joined
Apr 9, 2014
Messages
10
Hey guys!

I'm trying to get this COUNTIF function working properly to calculate how many of my employees I have closing on each day of the week. It already works properly for those who are OPENING each day, but I can't figure out what the issue is.

All of my cells are formatted as TIME.
My function for my openers is to show me how many individuals I have coming in at opening time which is 9:30AM.
=COUNTIF(E10:E37,"<=9:30")

That dunction works fine, but as soon as I change it to greater than OR equal to 6:30 (closing time) to calculate how many employees are staying until close - it breaks. If I use the following function it counts every cell that has any time in it whatsoever in the calculation:
=COUNTIF(F10:F37,">=6:30")

It seems it isn't differentiating between AM and PM. And if I try EQUAL to 6:30 it gives me a 0.

Help!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Yeah it seems my COUNTIF statement is assuming the 6:30 value is AM so anything greater than 6:30am gets added into the total. I need it to display only values greater than 6:30 PM.
 
Upvote 0
Try one of these:

Code:
[TABLE="width: 225"]
<colgroup><col></colgroup><tbody>[TR]
[TD]COUNTIF(F10:F37,">=18:30")[/TD]
[/TR]
[TR]
[TD]COUNTIF(F10:F37,">=6:30 PM")[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You're welcome. Thanks for the feedback.
In Excel time is a portion of a 24 hour day represented by a decimal. So, 8:00 AM in Excel is 8/24 or 0.33333. 6:30 PM would be 18.5/24 or 0.770833333. The above formula could be changed to:
COUNTIF(F10:F37,">="&18.5/24)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top