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
Acknowledged:
<tbody>
</tbody>
Responded:
<tbody>
</tbody>
Sample Data is as under in sheet 1:
<tbody>
</tbody>
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
- Acknowledged.
- Investigation
- Resolved.
- Date event happened (Column A of sample data)
- Time event occurred: (Column B of sample data)
- Time event responded to (Column D of sample data)
- Device on which the event occurred (Column G of sample data)
- Type of event (Column H of sample data)
- Ack Time (Column L) (This is based on Column D-B)
- Response (Resolved:….) (Column M)This is based on Column K where word Resolved occurs and the value is D-B in the same row
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>