HELP! EXCEL PROCESS TIME and BREAKS CALCULATION

shredraldz

New Member
Joined
Jul 16, 2013
Messages
4
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
ABCDEF
DATEPROCESS STARTPROCESS ENDDURATIONTYPE OF PROCESSTOTAL PROCESS TIME
07/16/20139:00 PM9:30 PM0:30A
07/16/20139:31 PM10:46 PM1:15A
07/16/201310:50 PM11:46 PM0:56C
07/16/201311:51 PM12:15 AM0:23D
07/17/201312:23 AM2:46 AM2:23B

<tbody>
</tbody>

SHEET 2: RECORDS OF BREAKS

<tbody>
</tbody>
ABCDE
DATEBREAK STARTBREAK ENDDURATIONTYPE OF BREAK
07/16/20139:40 PM9:55 PM0:15SHORT BREAK
07/16/201311:10 PM11:20 PM0:10BIO BREAK
07/17/201312:30 AM1:30 AM1:00LUNCH BREAK
07/17/20131:35 AM1:55 AM0:20TEAM 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! :LOL:


Thanks!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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
ABCDEF
DATEPROCESS STARTPROCESS ENDDURATIONTYPE OF PROCESSTOTAL PROCESS TIME
07/16/20139:00 PM9:30 PM0:30A
07/16/20139:31 PM10:46 PM1:15A
07/16/201310:50 PM11:46 PM0:56C
07/16/201311:51 PM12:15 AM0:23D
07/17/201312:23 AM2:46 AM2:23B

<tbody>
</tbody>

SHEET 2: RECORDS OF BREAKS

<tbody>
</tbody>
ABCDE
DATEBREAK STARTBREAK ENDDURATIONTYPE OF BREAK
07/16/20139:40 PM9:55 PM0:15SHORT BREAK
07/16/201311:10 PM11:20 PM0:10BIO BREAK
07/17/201312:30 AM1:30 AM1:00LUNCH BREAK
07/17/20131:35 AM1:55 AM0:20TEAM 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! :LOL:


Thanks!
 

Forum statistics

Threads
1,144,369
Messages
5,723,945
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top