Calculating average time for an event based on multiple conditions

vij

Board Regular
Joined
Feb 13, 2011
Messages
202
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:
NameACKNOWLEDGED P1 (Urgent) - Investigate ImmediatelyACKNOWLEDGED P2 (High) - Investigate within 2:00 minutesACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutesACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes
mogoyal0:02:020:00:000:02:11
magarwal0:01:350:01:400:01:22
sussaxen0:00:430:00:490:01:04

<tbody>
</tbody>











Responded:
NameACKNOWLEDGED P1 (Urgent) - Investigate ImmediatelyACKNOWLEDGED P2 (High) - Investigate within 2:00 minutesACKNOWLEDGED P3 (Normal) - Investigate within 5:00 minutesACKNOWLEDGED P4 (Low) - Investigate within 12:00 minutes
mogoyal0:04:470:11:300:06:39
magarwal0:03:350:04:250:03:36
sussaxen0:02:010:05:010:02:40

<tbody>
</tbody>











Sample Data is as under in sheet 1:

ABCDEFGHJKLMN
1alarm_happened_datealarm_happened_time_gmtalarm_respond_datealarm_respond_time_gmtalarm_process_statealarm_logical_statealarm_sourcealarm_typeoperator_idresponseAck TimeResponse TimeP2
201-07-201201:23:0301-07-201201:24:13Saved/PendingReset821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoormogoyalACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:01:10*
301-07-201201:23:0301-07-201201:24:23Saved/PendingReset821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoormogoyalINVESTIGATION - Began
4
01-07-201201:23:0301-07-201201:26:25Saved/PendingReset821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoormogoyalRESOLVED - OTHER:
Alam raiesd by security gaurd, to throw the garbage by the housekeeping
00:03:22
501-07-201201:23:0301-07-201201:26:30CompletedReset821-1-25 NOI:SERVICE LIFT FL7 DOAlarm Doormogoyal
601-07-201216:35:1701-07-201216:36:03Saved/PendingReset808-1-22 NOI:FLR4 FIRE STR 2Alarm DoormogoyalACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:00:46*
701-07-201216:35:1701-07-201216:36:08Saved/PendingReset808-1-22 NOI:FLR4 FIRE STR 2Alarm DoormogoyalINVESTIGATION - Began
801-07-201216:35:1701-07-201216:46:12CompletedReset808-1-22 NOI:FLR4 FIRE STR 2Alarm DoormogoyalAlarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location
901-07-201217:21:3701-07-201217:21:55Saved/PendingAlarm808-1-00 NOI:B1 ESR 2Door Held Open - SOXmogoyalACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:00:18*
1001-07-201217:21:3701-07-201217:22:00Saved/PendingAlarm808-1-00 NOI:B1 ESR 2Door Held Open - SOXmogoyalINVESTIGATION - Began
1101-07-201217:21:3701-07-201217:22:17CompletedAlarm808-1-00 NOI:B1 ESR 2Door Held Open - SOXmogoyalRESOLVED - DHO:Alarm raised due to movement of employees , confirmed from security guard at the location00:00:40
1201-07-201218:38:4201-07-201218:39:03Saved/PendingReset802-1-32 NOI:FLR6 FIRE STR 1Alarm DoormogoyalACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:00:21*
1301-07-201218:38:4201-07-201218:39:08Saved/PendingReset802-1-32 NOI:FLR6 FIRE STR 1Alarm DoormogoyalINVESTIGATION - Began
1410-07-201200:37:1610-07-201200:37:49Saved/PendingAlarm821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoorsussaxenINVESTIGATION - Began
Security staff alreday informed
1510-07-201200:37:1610-07-201200:37:32Saved/PendingAlarm821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoorsussaxenACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:00:16*
1610-07-201200:37:1610-07-201200:38:12CompletedAlarm821-1-25 NOI:SERVICE LIFT FL7 DOAlarm DoorsussaxenRESOLVED - OTHER:Door opened due to exiting the dust material00:00:56
1701-07-201218:38:4201-07-201218:39:47CompletedReset802-1-32 NOI:FLR6 FIRE STR 1Alarm DoormogoyalRESOLVED - OTHER:Alarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location00:01:05
1801-07-201222:58:5401-07-201223:13:59Saved/PendingReset802-1-26 NOI:FLR3 FIRE STR 1Alarm DoormogoyalACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes00:15:05*
1901-07-201222:58:5401-07-201223:14:07Saved/PendingReset802-1-26 NOI:FLR3 FIRE STR 1Alarm DoormogoyalINVESTIGATION - Began
2001-07-201222:58:5401-07-201223:14:30CompletedReset802-1-26 NOI:FLR3 FIRE STR 1Alarm DoormogoyalRESOLVED - OTHER:Alarm raised due to movement of Facility Staff, confirmed the same by sending the security guard to the location00:15:36

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top