HELP! EXCEL PROCESS TIME and BREAKS CALCULATION

[shredraldz]

Hi,

I'm looking for a way to calculate my working time spent for every type of process minus the total time spent on every type of break if I had it during that time range. I want excel to determine if I had a break while still working on a type of process.


For Example:


#1:
I started to work on process type A at 9:00 PM and finished working at 9:30 PM which results to a duration of 0:30. Based on the data on Sheet 2, I didn't had any type of break between 9:00 PM to 9:30 PM. So the Total Process Time should be 0:30 (same as duration).

#2:
I started to work on my 2nd process type A at 9:31 PM and finished working at 10:46 PM which results to a duration of 1:15. Based on the data on Sheet 2, I had a SHORT BREAK between 9:40 PM to 9:55 PM which results to a duration of 0:15. So the Total Process Time should be 1:00.


Please refer to the data on the sheets below.

SHEET 1: PROCESS TIME RECORDS
[TABLE="class: grid, width: 578"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]PROCESS START[/TD]
[TD]PROCESS END[/TD]
[TD]DURATION[/TD]
[TD]TYPE OF PROCESS[/TD]
[TD]TOTAL PROCESS TIME[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:00 PM[/TD]
[TD]9:30 PM[/TD]
[TD]0:30[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:31 PM[/TD]
[TD]10:46 PM[/TD]
[TD]1:15[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]10:50 PM[/TD]
[TD]11:46 PM[/TD]
[TD]0:56[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]11:51 PM[/TD]
[TD]12:15 AM[/TD]
[TD]0:23[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]12:23 AM[/TD]
[TD]2:46 AM[/TD]
[TD]2:23[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2: RECORDS OF BREAKS
[TABLE="width: 669"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 774"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]BREAK START[/TD]
[TD]BREAK END[/TD]
[TD]DURATION[/TD]
[TD]TYPE OF BREAK[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:40 PM[/TD]
[TD]9:55 PM[/TD]
[TD]0:15[/TD]
[TD]SHORT BREAK[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]11:10 PM[/TD]
[TD]11:20 PM[/TD]
[TD]0:10[/TD]
[TD]BIO BREAK[/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]12:30 AM[/TD]
[TD]1:30 AM[/TD]
[TD]1:00[/TD]
[TD]LUNCH BREAK[/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]1:35 AM[/TD]
[TD]1:55 AM[/TD]
[TD]0:20[/TD]
[TD]TEAM MEETING[/TD]
[/TR]
</tbody>[/TABLE]

I'm quite new in excel so please bare with me and HELP me solve this problem so I can sleep well. LOL!


Thanks!

 

[shredraldz]

HELP Please

 

[tusharm]

First, you have to convert the dates and times into a single date+time value. Otherwise, Excel has no way of knowing that activity D spanned midnight.

So, assume your data were in Sheet1 B2:G7 and Sheet2 B2:F6.

Then, in Sheet1 C11:H11 enter the literals
$C$11: PROCESS START
$D$11: PROCESS END
$E$11: DURATION
$F$11: TYPE OF PROCESS
$G$11: Break Duration
$H$11: TOTAL PROCESS TIME

In C12:F12 enter the formulas
$C$12: =B3+C3
$D$12: =B3+D3+(D3 < C3)
$E$12: =D12-C12
$F$12: =F3

We'll complete G:H later. Now, switch to Sheet2.

In C8:E8 enter the formulas
$C$8:=B3+C3
$D$8:=B3+D3+(D3 < C3)
$E$8:=D8-C8

Format the 1st 2 columns in each of the sheets as date+time and the 3rd column as time (hh:mm).
Copy C8:E8 down to rows 9:11.

Now, back to Sheet1.
Enter the array formula in $G$12: =SUM((Sheet2!$C$8:$C$11 > C12)*(Sheet2!$D$8:$D$11 < D12)*Sheet2!$E$8:$E$11)

To enter an array formula complete data entry with CTRL+SHIFT+ENTER rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula in curly brackets { and }

and the regular formula in $H$12: =E12-G12

Format both cells as time (hh:mm).

Copy C12:H12 down to rows 13:16 and you are done.

Hi,

I'm looking for a way to calculate my working time spent for every type of process minus the total time spent on every type of break if I had it during that time range. I want excel to determine if I had a break while still working on a type of process.


For Example:


#1:
I started to work on process type A at 9:00 PM and finished working at 9:30 PM which results to a duration of 0:30. Based on the data on Sheet 2, I didn't had any type of break between 9:00 PM to 9:30 PM. So the Total Process Time should be 0:30 (same as duration).

#2:
I started to work on my 2nd process type A at 9:31 PM and finished working at 10:46 PM which results to a duration of 1:15. Based on the data on Sheet 2, I had a SHORT BREAK between 9:40 PM to 9:55 PM which results to a duration of 0:15. So the Total Process Time should be 1:00.


Please refer to the data on the sheets below.

SHEET 1: PROCESS TIME RECORDS
[TABLE="class: grid, width: 578"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]PROCESS START[/TD]
[TD]PROCESS END[/TD]
[TD]DURATION[/TD]
[TD]TYPE OF PROCESS[/TD]
[TD]TOTAL PROCESS TIME[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:00 PM[/TD]
[TD]9:30 PM[/TD]
[TD]0:30[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:31 PM[/TD]
[TD]10:46 PM[/TD]
[TD]1:15[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]10:50 PM[/TD]
[TD]11:46 PM[/TD]
[TD]0:56[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]11:51 PM[/TD]
[TD]12:15 AM[/TD]
[TD]0:23[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]12:23 AM[/TD]
[TD]2:46 AM[/TD]
[TD]2:23[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2: RECORDS OF BREAKS
[TABLE="width: 669"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 774"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]BREAK START[/TD]
[TD]BREAK END[/TD]
[TD]DURATION[/TD]
[TD]TYPE OF BREAK[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]9:40 PM[/TD]
[TD]9:55 PM[/TD]
[TD]0:15[/TD]
[TD]SHORT BREAK[/TD]
[/TR]
[TR]
[TD]07/16/2013[/TD]
[TD]11:10 PM[/TD]
[TD]11:20 PM[/TD]
[TD]0:10[/TD]
[TD]BIO BREAK[/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]12:30 AM[/TD]
[TD]1:30 AM[/TD]
[TD]1:00[/TD]
[TD]LUNCH BREAK[/TD]
[/TR]
[TR]
[TD]07/17/2013[/TD]
[TD]1:35 AM[/TD]
[TD]1:55 AM[/TD]
[TD]0:20[/TD]
[TD]TEAM MEETING[/TD]
[/TR]
</tbody>[/TABLE]

I'm quite new in excel so please bare with me and HELP me solve this problem so I can sleep well. LOL!


Thanks!