Break Calculation

[om]

Start Time End Time TimeTaken ActualTime Break Start Break End Total Break
14:33:49 14:34:12 00:00:23 X 14:33:51 14:33:54 0:00:03
14:36:07 14:36:52 00:00:45 Y 14:33:58 14:34:02 0:00:04
14:34:07 14:34:10 0:00:03
14:34:16 14:34:19 0:00:04
14:36:16 14:36:21 0:00:05
14:36:25 14:36:30 0:00:05
14:36:34 14:36:37 0:00:03
14:36:42 14:36:46 0:00:04

Hi all

I have this sample data from which I have to calculate the ActualTime taken.
Which is calculated by the formula : ActualTime=TimeTaken-Total Break
For each Time Taken, there may be more than one break times which come in
the column Total Break.

Example:
For first row Time taken = End Time - Start Time which is 00:00:23
Actual Time = [Time Taken - Total Breaks between Start Time and End Time]
i.e. X = 00:00:23 - (0:00:03+0:00:04+0:00:03)
X = 00:00:23-(0:00:10)
X= 00:00:10
Similarly Y = 00:00:45-(0:00:05+0:00:05+0:00:03+0:00:04)
Y=00:00:28
I am looking for any formula or macro which can calculate X, Y taking into
consideration the start time and end time range. For any break if the break
start and break end time fall into any start time and end time range, the
break should be deducted from time taken.

Waiting for help...

Om

 

[tnazirov via OfficeKB.com]

Hi,

Below your data:

Start Time End Time TimeTaken ActualTime Break Start Break End Total Break
14:33:49 14:34:12 00:00:23 00:00:17 14:33:51 14:33:54 00:00:03
14:36:07 14:36:52 00:00:45 00:00:24 14:33:58 14:34:02 00:00:04
14:34:07 14:34:10 00:00:03 00:00:00 00:00:03
14:34:16 14:34:19 00:00:03 00:00:00 00:00:04
14:36:16 14:36:21 00:00:05 00:00:00 00:00:05
14:36:25 14:36:30 00:00:05 00:00:00 00:00:05
14:36:34 14:36:37 00:00:03 00:00:00 00:00:03
14:36:42 14:36:46 00:00:04 00:00:00 00:00:04

In column ActualTime I used formula:

=IF(C3-(SUMIF($A$2:$B$9,">="&A3,$G$2:$G$9)-SUMIF($A$2:$B$9,">="&B3,$G$2:$G$9))
<0,0,C3-(SUMIF($A$2:$B$9,">="&A3,$G$2:$G$9)-SUMIF($A$2:$B$9,">="&B3,$G$2:$G$9)
))

Best Regards.

Start Time End Time TimeTaken ActualTime Break Start Break End Total Break
14:33:49 14:34:12 00:00:23 X 14:33:51 14:33:54 0:00:03
14:36:07 14:36:52 00:00:45 Y 14:33:58 14:34:02 0:00:04
14:34:07 14:34:10 0:00:03
14:34:16 14:34:19 0:00:04
14:36:16 14:36:21 0:00:05
14:36:25 14:36:30 0:00:05
14:36:34 14:36:37 0:00:03
14:36:42 14:36:46 0:00:04

Hi all

I have this sample data from which I have to calculate the ActualTime taken.
Which is calculated by the formula : ActualTime=TimeTaken-Total Break
For each Time Taken, there may be more than one break times which come in
the column Total Break.

Example:
For first row Time taken = End Time - Start Time which is 00:00:23
Actual Time = [Time Taken - Total Breaks between Start Time and End Time]
i.e. X = 00:00:23 - (0:00:03+0:00:04+0:00:03)
X = 00:00:23-(0:00:10)
X= 00:00:10
Similarly Y = 00:00:45-(0:00:05+0:00:05+0:00:03+0:00:04)
Y=00:00:28
I am looking for any formula or macro which can calculate X, Y taking into
consideration the start time and end time range. For any break if the break
start and break end time fall into any start time and end time range, the
break should be deducted from time taken.

Waiting for help...

Om