how can any legal shift time be great then H2?

It can't! I just drag copied the first formula. It has no effect but the
formula could be simply:

=COUNTIF(D2:D58,"="&G2)

Biff

"Sandy Mann" wrote in message
...
"Biff" wrote in message
...
G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)

Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2)

Good point about 24:00 but how can any legal shift time be great then H2?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Biff" wrote in message
...
Hi!

When you use a 24 hour clock you should enter midnight as 24:00, not
0:00. Entering midnight as 0:00 evaluates to zero and there are no times
<0. You can format 24:00 to appear as 0:00 but the true underlying is not
changed. 24:00 evaluates to 1. Excel stores time as a fraction of a day.
A day is equal to 1 and midnight starts a new day. So.........

G1 = 6:30...............H1 = 15:00
G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)
G3 = 21:45.............H3 = 6:45

Day shift: =COUNTIF(D2:D58,"="&G1)-COUNTIF(D2:D21,""&H1)

Eve shift: =COUNTIF(D2:D58,"="&G2)-COUNTIF(D2:D58,""&H2)

Mid shift: =COUNTIF(D2:D58,"="G3)+COUNTIF(D2:D58,"<="H3)

Since you have overlapping shift times the total count of all occurrences
can be greater than the total number of entries in the range.

Biff

"meach741" wrote in message
oups.com...
I have looked for the past 3 hours for the answer....I know it is very
simple:

I have a column with various times in it. I am attempting to count the
number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve
Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45.

I have correctly (I think) calculated for Day and evening shift.
=COUNTIF(D2:D58,"<="&$C$71)-COUNTIF(D2:D58,"<"&$B$71)
where D2:D58 is col. containing the times and $C$71 and $B$71 are cells
I set up containing the range (6:30 is C71 and 15:30 is B71) for the
respective shift.

When I attempt this formula across the Mid Shift, I get a -49. Simple
math (and maybe its the easiest) tells me out of a range of 57 rows,
and Day returns a result of 13 and Eve returns 41, my answer should be
3. What formula do I use for calculating across the midnight hour?