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

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!A1 1),0,0))*(Sheet1!$A$1:$A$17<TIME(ROW(Sheet1!A12),0 ,0))*(Sheet1!$B$1:$B$17))

Mike

"Deepak Sharma" wrote:

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

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" wrote:

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!A1 1),0,0))*(Sheet1!$A$1:$A$17<TIME(ROW(Sheet1!A12),0 ,0))*(Sheet1!$B$1:$B$17))

Mike

"Deepak Sharma" wrote:

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

Are the data in Sheet 1 properly formatted times?

"Deepak Sharma" wrote:

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" wrote:

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!A1 1),0,0))*(Sheet1!$A$1:$A$17<TIME(ROW(Sheet1!A12),0 ,0))*(Sheet1!$B$1:$B$17))

Mike

"Deepak Sharma" wrote:

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