# Calculating average time for an event based on multiple conditions

#### vij

##### Board Regular
Hi,
I am manually calculating the undermentioned data and am tearing my hair out. I am sure there must be a better way of doing it. Please help....

I have to calculate the time taken for two parts of a task which has three/four steps. The steps are differentiated by specific words i.e

1. Acknowledged.
2. Investigation
3. Resolved.
The tasks are dependent on :

1. Date event happened (Column A of sample data)
2. Time event occurred: (Column B of sample data)
3. Time event responded to (Column D of sample data)
4. Device on which the event occurred (Column G of sample data)
5. Type of event (Column H of sample data)
The values to be calculated are:

1. Ack Time (Column L) (This is based on Column D-B)
2. Response (Resolved:….) (Column M)This is based on Column K where word Resolved occurs and the value is D-B in the same row
The output is required in the following format in sheet 2:
Acknowledged:
 Name ACKNOWLEDGED P1 (Urgent) - Investigate Immediately ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes ACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutes ACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes mogoyal 0:02:02 0:00:00 0:02:11 magarwal 0:01:35 0:01:40 0:01:22 sussaxen 0:00:43 0:00:49 0:01:04

<tbody>
</tbody>

Responded:
 Name ACKNOWLEDGED P1 (Urgent) - Investigate Immediately ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes ACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutes ACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes mogoyal 0:04:47 0:11:30 0:06:39 magarwal 0:03:35 0:04:25 0:03:36 sussaxen 0:02:01 0:05:01 0:02:40

<tbody>
</tbody>

Sample Data is as under in sheet 1:

 A B C D E F G H J K L M N 1 alarm_happened_date alarm_happened_time_gmt alarm_respond_date alarm_respond_time_gmt alarm_process_state alarm_logical_state alarm_source alarm_type operator_id response Ack Time Response Time P2 2 01-07-2012 01:23:03 01-07-2012 01:24:13 Saved/Pending Reset 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door mogoyal ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:01:10 * 3 01-07-2012 01:23:03 01-07-2012 01:24:23 Saved/Pending Reset 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door mogoyal INVESTIGATION - Began 4 01-07-2012 01:23:03 01-07-2012 01:26:25 Saved/Pending Reset 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door mogoyal RESOLVED - OTHER: Alam raiesd by security gaurd, to throw the garbage by the housekeeping 00:03:22 5 01-07-2012 01:23:03 01-07-2012 01:26:30 Completed Reset 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door mogoyal 6 01-07-2012 16:35:17 01-07-2012 16:36:03 Saved/Pending Reset 808-1-22 NOI:FLR4 FIRE STR 2 Alarm Door mogoyal ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:00:46 * 7 01-07-2012 16:35:17 01-07-2012 16:36:08 Saved/Pending Reset 808-1-22 NOI:FLR4 FIRE STR 2 Alarm Door mogoyal INVESTIGATION - Began 8 01-07-2012 16:35:17 01-07-2012 16:46:12 Completed Reset 808-1-22 NOI:FLR4 FIRE STR 2 Alarm Door mogoyal Alarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location 9 01-07-2012 17:21:37 01-07-2012 17:21:55 Saved/Pending Alarm 808-1-00 NOI:B1 ESR 2 Door Held Open - SOX mogoyal ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:00:18 * 10 01-07-2012 17:21:37 01-07-2012 17:22:00 Saved/Pending Alarm 808-1-00 NOI:B1 ESR 2 Door Held Open - SOX mogoyal INVESTIGATION - Began 11 01-07-2012 17:21:37 01-07-2012 17:22:17 Completed Alarm 808-1-00 NOI:B1 ESR 2 Door Held Open - SOX mogoyal RESOLVED - DHO:Alarm raised due to movement of employees , confirmed from security guard at the location 00:00:40 12 01-07-2012 18:38:42 01-07-2012 18:39:03 Saved/Pending Reset 802-1-32 NOI:FLR6 FIRE STR 1 Alarm Door mogoyal ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:00:21 * 13 01-07-2012 18:38:42 01-07-2012 18:39:08 Saved/Pending Reset 802-1-32 NOI:FLR6 FIRE STR 1 Alarm Door mogoyal INVESTIGATION - Began 14 10-07-2012 00:37:16 10-07-2012 00:37:49 Saved/Pending Alarm 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door sussaxen INVESTIGATION - Began Security staff alreday informed 15 10-07-2012 00:37:16 10-07-2012 00:37:32 Saved/Pending Alarm 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door sussaxen ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:00:16 * 16 10-07-2012 00:37:16 10-07-2012 00:38:12 Completed Alarm 821-1-25 NOI:SERVICE LIFT FL7 DO Alarm Door sussaxen RESOLVED - OTHER:Door opened due to exiting the dust material 00:00:56 17 01-07-2012 18:38:42 01-07-2012 18:39:47 Completed Reset 802-1-32 NOI:FLR6 FIRE STR 1 Alarm Door mogoyal RESOLVED - OTHER:Alarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location 00:01:05 18 01-07-2012 22:58:54 01-07-2012 23:13:59 Saved/Pending Reset 802-1-26 NOI:FLR3 FIRE STR 1 Alarm Door mogoyal ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes 00:15:05 * 19 01-07-2012 22:58:54 01-07-2012 23:14:07 Saved/Pending Reset 802-1-26 NOI:FLR3 FIRE STR 1 Alarm Door mogoyal INVESTIGATION - Began 20 01-07-2012 22:58:54 01-07-2012 23:14:30 Completed Reset 802-1-26 NOI:FLR3 FIRE STR 1 Alarm Door mogoyal RESOLVED - OTHER:Alarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location 00:15:36

<tbody>
</tbody>

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### vij

##### Board Regular
I am using excel 2010.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,669
Messages
5,838,685
Members
430,563
Latest member
Raeyven

### 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.

### Which adblocker are you using?

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

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