software

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(D258,quot;lt;=quot;amp;$C$71)-COUNTIF(D258,quot;lt;quot;amp;$B$71)
where D258 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?In the first COUNTIF
,quot;lt;=quot;amp;$C$71 with C71= 21:45 all shift starting time will satisfy the
condition

In the second COUNTIF
,quot;lt;quot;amp;$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(D258,$C$71)

--
HTH

Sandy
with @tiscali.co.uk

quot;meach741quot; gt; wrote in message oups.com...
gt;I have looked for the past 3 hours for the answer....I know it is very
gt; simple:
gt;
gt; I have a column with various times in it. I am attempting to count the
gt; number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve
gt; Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45.
gt;
gt; I have correctly (I think) calculated for Day and evening shift.
gt; =COUNTIF(D258,quot;lt;=quot;amp;$C$71)-COUNTIF(D258,quot;lt;quot;amp;$B$71)
gt; where D258 is col. containing the times and $C$71 and $B$71 are cells
gt; I set up containing the range (6:30 is C71 and 15:30 is B71) for the
gt; respective shift.
gt;
gt; When I attempt this formula across the Mid Shift, I get a -49. Simple
gt; math (and maybe its the easiest) tells me out of a range of 57 rows,
gt; and Day returns a result of 13 and Eve returns 41, my answer should be
gt; 3. What formula do I use for calculating across the midnight hour?
gt;
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 lt;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(D258,quot;gt;=quot;amp;G1)-COUNTIF(D221,quot;gt;quot;amp;H1)

Eve shift: =COUNTIF(D258,quot;gt;=quot;amp;G2)-COUNTIF(D258,quot;gt;quot;amp;H2)

Mid shift: =COUNTIF(D258,quot;gt;=quot;G3) COUNTIF(D258,quot;lt;=quot;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

quot;meach741quot; gt; wrote in message oups.com...
gt;I have looked for the past 3 hours for the answer....I know it is very
gt; simple:
gt;
gt; I have a column with various times in it. I am attempting to count the
gt; number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve
gt; Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45.
gt;
gt; I have correctly (I think) calculated for Day and evening shift.
gt; =COUNTIF(D258,quot;lt;=quot;amp;$C$71)-COUNTIF(D258,quot;lt;quot;amp;$B$71)
gt; where D258 is col. containing the times and $C$71 and $B$71 are cells
gt; I set up containing the range (6:30 is C71 and 15:30 is B71) for the
gt; respective shift.
gt;
gt; When I attempt this formula across the Mid Shift, I get a -49. Simple
gt; math (and maybe its the easiest) tells me out of a range of 57 rows,
gt; and Day returns a result of 13 and Eve returns 41, my answer should be
gt; 3. What formula do I use for calculating across the midnight hour?
gt;
quot;Biffquot; gt; wrote in message
...
gt; G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)

gt; Eve shift: =COUNTIF(D258,quot;gt;=quot;amp;G2)-COUNTIF(D258,quot;gt;quot;amp;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.ukquot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; When you use a 24 hour clock you should enter midnight as 24:00, not 0:00.
gt; Entering midnight as 0:00 evaluates to zero and there are no times lt;0. You
gt; can format 24:00 to appear as 0:00 but the true underlying is not changed.
gt; 24:00 evaluates to 1. Excel stores time as a fraction of a day. A day is
gt; equal to 1 and midnight starts a new day. So.........
gt;
gt; G1 = 6:30...............H1 = 15:00
gt; G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)
gt; G3 = 21:45.............H3 = 6:45
gt;
gt; Day shift: =COUNTIF(D258,quot;gt;=quot;amp;G1)-COUNTIF(D221,quot;gt;quot;amp;H1)
gt;
gt; Eve shift: =COUNTIF(D258,quot;gt;=quot;amp;G2)-COUNTIF(D258,quot;gt;quot;amp;H2)
gt;
gt; Mid shift: =COUNTIF(D258,quot;gt;=quot;G3) COUNTIF(D258,quot;lt;=quot;H3)
gt;
gt; Since you have overlapping shift times the total count of all occurrences
gt; can be greater than the total number of entries in the range.
gt;
gt; Biff
gt;
gt; quot;meach741quot; gt; wrote in message
gt; oups.com...
gt;gt;I have looked for the past 3 hours for the answer....I know it is very
gt;gt; simple:
gt;gt;
gt;gt; I have a column with various times in it. I am attempting to count the
gt;gt; number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve
gt;gt; Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45.
gt;gt;
gt;gt; I have correctly (I think) calculated for Day and evening shift.
gt;gt; =COUNTIF(D258,quot;lt;=quot;amp;$C$71)-COUNTIF(D258,quot;lt;quot;amp;$B$71)
gt;gt; where D258 is col. containing the times and $C$71 and $B$71 are cells
gt;gt; I set up containing the range (6:30 is C71 and 15:30 is B71) for the
gt;gt; respective shift.
gt;gt;
gt;gt; When I attempt this formula across the Mid Shift, I get a -49. Simple
gt;gt; math (and maybe its the easiest) tells me out of a range of 57 rows,
gt;gt; and Day returns a result of 13 and Eve returns 41, my answer should be
gt;gt; 3. What formula do I use for calculating across the midnight hour?
gt;gt;
gt;
gt;

If D258 contains simple times (without dates) then you can use this
formula

=SUMPRODUCT(--((D$2$58gt;G2) (D$2$58lt;H2) (G2gt;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)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=530219gt;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(D258,quot;gt;=quot;amp;G2)

Biff

quot;Sandy Mannquot; gt; wrote in message
...
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)
gt;
gt;gt; Eve shift: =COUNTIF(D258,quot;gt;=quot;amp;G2)-COUNTIF(D258,quot;gt;quot;amp;H2)
gt;
gt; Good point about 24:00 but how can any legal shift time be great then H2?
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; Hi!
gt;gt;
gt;gt; When you use a 24 hour clock you should enter midnight as 24:00, not
gt;gt; 0:00. Entering midnight as 0:00 evaluates to zero and there are no times
gt;gt; lt;0. You can format 24:00 to appear as 0:00 but the true underlying is not
gt;gt; changed. 24:00 evaluates to 1. Excel stores time as a fraction of a day.
gt;gt; A day is equal to 1 and midnight starts a new day. So.........
gt;gt;
gt;gt; G1 = 6:30...............H1 = 15:00
gt;gt; G2 = 15:00.............H2 = 24:00 (formated to appear as 0:00)
gt;gt; G3 = 21:45.............H3 = 6:45
gt;gt;
gt;gt; Day shift: =COUNTIF(D258,quot;gt;=quot;amp;G1)-COUNTIF(D221,quot;gt;quot;amp;H1)
gt;gt;
gt;gt; Eve shift: =COUNTIF(D258,quot;gt;=quot;amp;G2)-COUNTIF(D258,quot;gt;quot;amp;H2)
gt;gt;
gt;gt; Mid shift: =COUNTIF(D258,quot;gt;=quot;G3) COUNTIF(D258,quot;lt;=quot;H3)
gt;gt;
gt;gt; Since you have overlapping shift times the total count of all occurrences
gt;gt; can be greater than the total number of entries in the range.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;meach741quot; gt; wrote in message
gt;gt; oups.com...
gt;gt;gt;I have looked for the past 3 hours for the answer....I know it is very
gt;gt;gt; simple:
gt;gt;gt;
gt;gt;gt; I have a column with various times in it. I am attempting to count the
gt;gt;gt; number of occurences per shift, ex: Day Shift is 6:30 - 15:30, Eve
gt;gt;gt; Shift is 15:00 - 0:00 and Mid Shift is 21:45 - 6:45.
gt;gt;gt;
gt;gt;gt; I have correctly (I think) calculated for Day and evening shift.
gt;gt;gt; =COUNTIF(D258,quot;lt;=quot;amp;$C$71)-COUNTIF(D258,quot;lt;quot;amp;$B$71)
gt;gt;gt; where D258 is col. containing the times and $C$71 and $B$71 are cells
gt;gt;gt; I set up containing the range (6:30 is C71 and 15:30 is B71) for the
gt;gt;gt; respective shift.
gt;gt;gt;
gt;gt;gt; When I attempt this formula across the Mid Shift, I get a -49. Simple
gt;gt;gt; math (and maybe its the easiest) tells me out of a range of 57 rows,
gt;gt;gt; and Day returns a result of 13 and Eve returns 41, my answer should be
gt;gt;gt; 3. What formula do I use for calculating across the midnight hour?
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
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