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!
 

Some videos you may like

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
Joined
Apr 9, 2014
Messages
10
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
Joined
Oct 10, 2011
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 9, 2014
Messages
10
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
Joined
Oct 10, 2011
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,266
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top