Calculating average time for an event based on multiple conditions

[vij]

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:
[TABLE="class: grid, width: 637, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ACKNOWLEDGED P1 (Urgent) - Investigate Immediately[/TD]
[TD]ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes[/TD]
[TD]ACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutes[/TD]
[TD]ACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes[/TD]
[/TR]
[TR]
[TD]mogoyal[/TD]
[TD][/TD]
[TD]0:02:02[/TD]
[TD]0:00:00[/TD]
[TD]0:02:11[/TD]
[/TR]
[TR]
[TD]magarwal[/TD]
[TD][/TD]
[TD]0:01:35[/TD]
[TD]0:01:40[/TD]
[TD]0:01:22[/TD]
[/TR]
[TR]
[TD]sussaxen[/TD]
[TD][/TD]
[TD]0:00:43[/TD]
[TD]0:00:49[/TD]
[TD]0:01:04[/TD]
[/TR]
</tbody>[/TABLE]











Responded:
[TABLE="class: grid, width: 637, align: left"]
<tbody>[TR]
[TD] Name[/TD]
[TD]ACKNOWLEDGED P1 (Urgent) - Investigate Immediately[/TD]
[TD]ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes[/TD]
[TD]ACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutes[/TD]
[TD]ACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes[/TD]
[/TR]
[TR]
[TD]mogoyal[/TD]
[TD][/TD]
[TD]0:04:47[/TD]
[TD]0:11:30[/TD]
[TD]0:06:39[/TD]
[/TR]
[TR]
[TD]magarwal[/TD]
[TD][/TD]
[TD]0:03:35[/TD]
[TD]0:04:25[/TD]
[TD]0:03:36[/TD]
[/TR]
[TR]
[TD]sussaxen[/TD]
[TD][/TD]
[TD]0:02:01[/TD]
[TD]0:05:01[/TD]
[TD]0:02:40[/TD]
[/TR]
</tbody>[/TABLE]











Sample Data is as under in sheet 1:

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

 

[vij]

I am using excel 2010.