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

A	B	C	D	E	F
DATE	PROCESS START	PROCESS END	DURATION	TYPE OF PROCESS	TOTAL PROCESS TIME
07/16/2013	9:00 PM	9:30 PM	0:30	A
07/16/2013	9:31 PM	10:46 PM	1:15	A
07/16/2013	10:50 PM	11:46 PM	0:56	C
07/16/2013	11:51 PM	12:15 AM	0:23	D
07/17/2013	12:23 AM	2:46 AM	2:23	B

<tbody>
</tbody>

SHEET 2: RECORDS OF BREAKS

<tbody>
</tbody>

A	B	C	D	E
DATE	BREAK START	BREAK END	DURATION	TYPE OF BREAK
07/16/2013	9:40 PM	9:55 PM	0:15	SHORT BREAK
07/16/2013	11:10 PM	11:20 PM	0:10	BIO BREAK
07/17/2013	12:30 AM	1:30 AM	1:00	LUNCH BREAK
07/17/2013	1:35 AM	1:55 AM	0:20	TEAM MEETING

<tbody>
</tbody>

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

A	B	C	D	E	F
DATE	PROCESS START	PROCESS END	DURATION	TYPE OF PROCESS	TOTAL PROCESS TIME
07/16/2013	9:00 PM	9:30 PM	0:30	A
07/16/2013	9:31 PM	10:46 PM	1:15	A
07/16/2013	10:50 PM	11:46 PM	0:56	C
07/16/2013	11:51 PM	12:15 AM	0:23	D
07/17/2013	12:23 AM	2:46 AM	2:23	B

<tbody>
</tbody>

SHEET 2: RECORDS OF BREAKS

<tbody>
</tbody>

A	B	C	D	E
DATE	BREAK START	BREAK END	DURATION	TYPE OF BREAK
07/16/2013	9:40 PM	9:55 PM	0:15	SHORT BREAK
07/16/2013	11:10 PM	11:20 PM	0:10	BIO BREAK
07/17/2013	12:30 AM	1:30 AM	1:00	LUNCH BREAK
07/17/2013	1:35 AM	1:55 AM	0:20	TEAM MEETING

<tbody>
</tbody>

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


Thanks!