# COUNTIF function issue

#### thesanealien

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### thesanealien

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

#### AhoyNC

##### Well-known Member
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]``````

#### thesanealien

##### New Member
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]``````

It was in military time! Thanks!

#### AhoyNC

##### Well-known Member
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)

Replies
8
Views
73
Replies
15
Views
82
Replies
1
Views
60
Replies
3
Views
51
Replies
0
Views
111