Count of a time range

[Nycole]

I have a list of times 0:00 - 24:00 and days and I need to find out how many
files were processed between in an 8 hour shift per day

Entry Number TIME Est. Date of Arrival
9065233-5 14:56 1/1/2008
9065234-3 15:11 1/1/2008
9065235-0 15:26 1/1/2008
9065240-0 16:02 1/1/2008
7082203-1 18:30 1/1/2008
9065245-9 1:01 1/2/2008
3705209-4 1:41 1/2/2008
3705220-1 3:05 1/2/2008

 

[Bob Phillips]

=SUMPRODUCT(--(time_range>=TIME(8,0,0)),--(time_range<=TIME(16,0,0))

the time_range must be an explicit range it cannot be whole columns.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nycole]

How do I put in the column range that I am pulling this information from?

 

[Nycole]

I am using the following and I am getting #NAME?

=SUMPRODUCT(--(time_range>=Sheet4!C:C(8,0,0)),--(time_range<=Sheet4!C:C(16,0,0)))

 

[Teethless mama]

Don't use a whole column unless you use XL-2007

=SUMPRODUCT(--(Sheet4!C1:C1000>=TIME(8,0,0)),--(Sheet4!C1:C1000<=TIME(16,0,0))

 

[Bob Phillips]

time_range is the cells to be tested so it is those that you must change. I
also mentioned that time_range must be an explicit range it cannot be whole
columns.

The TIME statement was supplying the times to be checked against (8AM and
4PM in my example), so you should only adjust these parts if you want
different times, don't remove the TIME function.

In summary, your test should be

=SUMPRODUCT(--(Sheet4!C2:C200>=TIME(8,0,0)),--(Sheet4!C2:C200<=TIME(16,0,0)))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nycole]

That has worked, but now I still need to break it down even further. I need
to know how many in an 8 hour shift per one day.

 

[Pete_UK]

You will need three formulae, like this:

=SUMPRODUCT(--(Sheet4!C2:C200>=TIME(0,0,0)),--(Sheet4!
C2:C200<=TIME(8,0,0)­))

=SUMPRODUCT(--(Sheet4!C2:C200>=TIME(8,0,0)),--(Sheet4!
C2:C200<=TIME(16,0,0)­))

=SUMPRODUCT(--(Sheet4!C2:C200>=TIME(16,0,0)),--(Sheet4!
C2:C200<=TIME(23,59,59)­))

The first formula will count the shift between midnight and 8:00am,
the second between 8:00am and 4:00pm, and the third between 4:00pm and
midnight.

Hope this helps.

Pete

 

[Nycole]

Actually it didn't work the 12-8 did but the 8-4 and the 4-12 didn't. What
am I doing wrong?

 

[Nycole]

Yeah!! That worked but i need to add in per day. I have a 30 day list of
hours and a row next to it that goes with the day. I need to break it out
that I have XX entires on the 1st for the 12-8 and XX entries on the 1st for
the 8-4 etc etc per day per shift

 

[Pete_UK]

You said in your reply to Bob that his formula worked - that covered
the period 8:00am to 4:00pm.

Make sure that the ranges in your formulae are still correct - if you
have copied them to other cells then the ranges will have adjusted as
they do not use absolute addressing.

Also, check that you do have proper times, and that they do represent
hours:minutes (and not minutes:seconds.

Hope this helps.

Pete

 

[Nycole]

What you wrote worked but I still need to break it up by day. How do I do
that?

 

[Bob Phillips]

Assuming the date is in column B

=SUMPRODUCT(--(Sheet4!B2:B200=--"2008-01-22"),--(Sheet4!C2:C200>=TIME(8,0,0)),--(Sheet4!C2:C200<=TIME(16,0,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

If you would like the results as table of 30 rows (one for each day)
and three columns (one for each shift), then you could put the first
date in, say, S2, then in S3 you can have this formula:

=S2+1

which can be copied down to S31 to give you the list of dates. Then
put this formula in T2:

=SUMPRODUCT(--(Sheet4!$D$2:$D$200=$S2),--(Sheet4!$C$2:$C
$200>=TIME(0,0,0)),--(Sheet4!$C$2:$C$200<=TIME(8,0,0)­))

Copy the formula into U2 and V2, but make the necessary changes to the
TIME terms to cover the other shifts:

U2: =SUMPRODUCT(--(Sheet4!$D$2:$D$200=$S2),--(Sheet4!$C$2:$C
$200>=TIME(8,0,0)),--(Sheet4!$C$2:$C$200<=TIME(16,0,0)­))

V2: =SUMPRODUCT(--(Sheet4!$D$2:$D$200=$S2),--(Sheet4!$C$2:$C
$200>=TIME(16,0,0)),--(Sheet4!$C$2:$C$200<=TIME(23,59,59)­))

Now you can copy T2:V2 down to row 31 to have your table of values.
You should change the cell references to cover the extent of your
data, but do not try to use a full column reference.

Hope this helps.

Pete