Countif Across the Midnight Hour

[meach741]

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?

[Sandy Mann]

In the first COUNTIF
,"<="&$C$71 with C71= 21:45 all shift starting time will satisfy the
condition

In the second COUNTIF
,"<"&$B$71 with 15:30 in B71 all Early and Midnight shifts will satisfy the
condition

so you will get erroneos results.

If shift times are entered exacly then why not just check for start times?

=COUNTIF(D2:D58,$C$71)

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"meach741" <[email protected]> wrote in message
news:[email protected]...
>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?
>

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
>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?
>

[Sandy Mann]

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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

[email protected]
[email protected] with @tiscali.co.uk


"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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?
>>
>
>

[daddylonglegs]

If D2:D58 contains simple times (without dates) then you can use this formula

=SUMPRODUCT(--((D$2:D$58>G2)+(D$2:D$58<H2)+(G2>H2)=2))

to count the number within each shift where G2 contains the start of the range (e.g. 06:00) and H2 the end of the range (e.g. 15:00)

[Biff]

>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" <[email protected]> wrote in message
news:[email protected]...
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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?
>>>
>>
>>
>
>

[meach741]

Thanks to all who replied: Sandy, Biff and Daddylonglegs. I used the
SUMIF formula and it worked great. I tried the others and they worked
as well. The SUMIF Formula was just easier to copy and paste across my
12 spreadsheets with having to make the corrections for the cell
references.

I'm sure I will be back with other questions.

Thanks again,

Tim