COUNTIF function issue

[thesanealien]

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!

 

[thesanealien]

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]

Try one of these:

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

Try one of these:

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

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)