Time Duration Calculation help needed

[Deepak Sharma]

I have A and B Coloums
A - coloum shows task completed at what time
B - coloum shows how many task completed

Sheet 1

A B
11:45 1
12:00 2
12:15 4
12:30 0
12:45 1
13:00 1
13:15 1
13:30 2
13:45 4
14:00 0
14:15 2
14:45 1
15:00 1
15:15 1
16:00 1
16:15 1
16:30 2

I need to get the result as shown as in Sheet 2,
Coloum A - Duration in hours
Coloum B - Number of task completed in that duration from Sheet 1

Sheet 2

A B
11:00-12:00 1
12:00-13:00 8
13:00-14:00 7
14:00-15:00 4
15:00-16:00 2
16:00-17:00 3


any help is appreciated

 

[Mike H]

Hi,

Put this on sheet 2 and drag down but not I get different answers than you.
For example 12:00 to 13:00 is 7 not eight. The reason for this is in the
11:00 to 12:00 you have excluded the task completed at 12:00.

=SUMPRODUCT((Sheet1!$A$1:$A$17>=TIME(ROW(Sheet1!A11),0,0))*(Sheet1!$A$1:$A$17<TIME(ROW(Sheet1!A12),0,0))*(Sheet1!$B$1:$B$17))

Mike

 

[Deepak Sharma]

Hi Mike,
the values are not showing up, its coming as 0

I pasted the values as below in sheet1- but the formula not working, I
pasted the formula in sheet2 Cell B2 onwards

00:45 1
03:00 1
07:30 2
08:00 1
08:15 2
08:30 3
08:45 2
09:00 2
09:15 3
09:30 3
09:45 2
10:00 3
10:30 2
10:45 3
11:00 1
11:15 2
11:30 2
12:00 2
12:15 2
12:30 1
12:45 1
13:15 2
14:15 3
14:30 5
14:45 1
15:00 1
15:30 1
15:45 1
16:00 2
16:15 1

 

[Mike H]

Are the data in Sheet 1 properly formatted times?

 

[Deepak Sharma]

Hi Mike,

I am not getting the right results - let me redo the scenario

The Sheet1 will have the data as follows and it differs every day

- So for 19th Oct the job started at 06:00 AM and on 20th Oct at 00:45

for 19th October the Task completed - will be as follows
A B
06:00 3
07:30 1
08:00 1
08:15 1
08:45 1
09:00 1
09:15 3
09:30 2
09:45 3
10:00 4
10:15 2
10:30 1
10:45 2
11:00 1
11:15 4
11:45 1
12:00 2
12:15 4
12:30 0
12:45 1
13:00 1
13:15 1
13:30 2
13:45 4
14:00 0
14:15 2
14:45 1
15:00 1
15:15 1
16:00 1
16:15 1
16:30 2
16:45 1
17:00 1
17:30 1
17:45 1
18:15 1
18:30 0
20:15 1
---------------------------------------
For 20th Oct - will be as follows
---------------------------------------
A B
00:45 1
03:00 1
07:30 2
08:00 1
08:15 2
08:30 3
08:45 2
09:00 2
09:15 3
09:30 3
09:45 2
10:00 3
10:30 2
10:45 3
11:00 1
11:15 2
11:30 2
12:00 2
12:15 2
12:30 1
12:45 1
13:15 2
14:15 3
14:30 5
14:45 1
15:00 1
15:30 1
15:45 1
16:00 2
16:15 1
---------------------------------------
For 21st Oct - will be as follows
---------------------------------------
A B
07:30 1
08:00 2
08:15 0
08:45 2
09:00 1
09:15 5
09:30 0
09:45 2
10:00 1
10:15 3
10:30 4
10:45 1
11:00 3
11:15 2
11:30 1
11:45 2
12:15 1
13:00 2
14:00 1
14:15 1
14:45 2
15:15 2
15:45 2
16:00 1
17:00 1
17:15 1
18:00 1
21:00 1
21:15 1
21:30 0
21:45 1
*******************************************************
The Result on sheet 2 will be as follows

A B C D
E
19-Oct 20-Oct 21-Oct 22-Oct
00:00-01:00
01:00-02:00
02:00-03:00
03:00-04:00
04:00-05:00
05:00-06:00
06:00-07:00
07:00-08:00
08:00-09:00
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00
13:00-14:00
14:00-15:00
15:00-16:00
16:00-17:00
17:00-18:00
18:00-19:00
19:00-20:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00

So B2 will have the jobs completed on 19th Oct between 00:00-01:00 and so on