Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Break Calculation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Break Calculation
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. om wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Break Calculation | Excel Discussion (Misc queries) | |||
Break a link between workbooks when there is no "break" option | Excel Discussion (Misc queries) | |||
Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |