Time series

M

mohitmahajan

I have several tabs for different dates with data in it. Colomn
contains the time in format 13:30:30.

For my project, I require to know the number of data points in variou
time series, pls see below.

7:30:00 8:30:00
8:30:00 9:30:00
9:30:00 10:30:00
10:30:00 11:30:00
11:30:00 12:30:00
12:30:00 13:30:00
13:30:00 14:30:00
14:30:00 15:30:00
15:30:00 16:00:00

I tried countif and if function but could not come up with the results
PLS HELP. If I have to do it manually then I am dead.....
 
B

Bob Phillips

Do you mean something along the lines of

=SUMPRODUCT(--(G$2:$G$1000>=TIME(7,30,0)),--(G$2:$G$1000<TIME(8,30,0)))

etc.

Best to put the comparison times in cells and use say

=SUMPRODUCT(--($G$2:$G$1000>=A1),--($G$2:$G$1000<B1))

which can then be copied easily

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan" <[email protected]>
wrote in message
news:[email protected]...
 
M

mohitmahajan

Ok, here is the 2nd part of the problem.....

Now I have been asked to take out data per associate hour wise.....
I have attached the sheet with the table also in which info i
required....

Pls help:confused

+-------------------------------------------------------------------
|Filename: Book2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4117
+-------------------------------------------------------------------
 
M

mohitmahajan

I tried :

=+COUNTIF(*$A$2*:$A$664,AND($A$2,SUMPRODUCT(--($G$2:$G$1000>=K2),--($G$2:$G$1000<L2))))

but this did not work even though it did not give me any error. The
value returned here for all time series/groups was 0.
The cell in bold is the name reference....

Pls help....
 
B

Bob Phillips

As far as I can see, there are no names associated with the times, so you
cannot get an analysis by time by name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan" <[email protected]>
wrote in message
news:[email protected]...
 
M

mohitmahajan

:) Got it....
Now what I did was

=IF(AND(G244<$N$2,G244>=$M$2),"7:30 till
8:30",IF(AND(G244<$N$8,G244>=$M$8),"13:30 to
14:30",IF(AND(G244<$N$7,G244>=$M$7),"12:30 to
13:30",IF(AND(G244<$N$6,G244>=$M$6),"11:30 to
12:30",IF(AND(G244<$N$5,G244>=$M$5),"10:30 to
11:30",IF(AND(G244<$N$4,G244>=$M$4),"9:30 to
10:30",IF(AND(G244>=$M$3,G244<$N$3),"8:30 to 9:30")))))))

I copied this and got each data point in a series and then did a pivot
on them and got hour wise time spent and hour wise data points for each
associate/team member......

Thanks Bob for your patience.....:)
 
Top