Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Break Calculation Macro
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.programming
|
|||
|
|||
Break Calculation Macro
The code below assumes the Test Start is at location A2 and the Test End is at B2 and the Time Taken is in C2. Set BreakStart = Range("E$2:E$9") Set BreakEnd = Range("F$2:F$9") Set TotalBreak = Range("G$2:G$9") ActualTime = C2 - sumproduct(--(BreakStart = A2),--(BreakEnd =< B2),BotalBreak) This is the simple case where the break times always fall between the Start Time and End Time. The problem gets more complicated if you have the following Start Time End Time TimeTaken 14:33:49 14:34:12 00:00:23 ActualTime Break Start Break End Total Break 14:34:00 14:35:00 1:00 The above case the break is at the end of the test time and continues past the end of the test. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166390 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Break Calculation | Excel Discussion (Misc queries) | |||
Break Calculation | Excel Worksheet Functions | |||
Break Links Macro help... | Excel Programming | |||
Add break in macro | Excel Programming | |||
Can Not Get Macro to Break with CTRL-BREAK | Excel Programming |