Issue using formula to count time instances

pdevil

New member
Joined
Jan 22, 2017
Messages
1
Reaction score
0
Points
0
Hi All,

I'm trying to count the number of cells where the time is before or after a certain time value. The formula I'm entering right now is :

=COUNTIF(Sheet3!V:V,"<11:00:00 AM")

This is giving me "0" as a result. This is to count the number of cells where the time value is less than 11:00 a.m. However, if I change it to the below, I'm getting a clear result:

=COUNTIF(Sheet3!V:V,">11:00:00 AM")

This will give the the count of cells where the time value is more than 11:00 a.m.

FYI, I've tried changing the options to use 1904 date system just to see, but the result is the same.

Would really appreciate some help here.

:pray2:
 
You can't put criteria thats meant to be a numeric value in "" as you have done. Awkwardly if you need to use <or > with a number, these are not numeric characters. To cope with this Excel uses a VBA technique to join these with a number.
So you could go:

Code:
=COUNTIF(Sheet3!V:V, ">" & 0.458333333333333)
But if you dont want to do the maths, :) Then:
Code:
=COUNTIF(Sheet3!V:V, ">" & TIME(11,0,0))
 
Back
Top