Count of a time range

N

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
 
B

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)
 
N

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)))
 
T

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))
 
B

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)
 
N

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.
 
P

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
 
N

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?
 
N

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
 
P

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
 
B

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)
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top