Calculating average time for an event based on multiple conditions

V

vij

Board Regular
Joined
Feb 13, 2011
Messages
215
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>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

B
  • Question
average based on time
Replies
1
Views
279
baqer
B
PS_Richard
  • Solved
Calculate Time Difference Per Day Based On Criteria
Replies
5
Views
576
Anthony47
A
D
  • Question
Calculate turnaround time based on multiple criteria
Replies
0
Views
498
darejack2001
D
V
  • Question
Search for text and sort data
Replies
0
Views
294
vij
V
V
  • Question
Count number of time a time value increases for a particular condition
Replies
2
Views
361
vij
V

Forum statistics

Threads
1,214,584
Messages
6,120,384
Members
448,956
Latest member
JPav

Share this page

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
Back
Top