Hi Guy
Am Having an issue around this, i need to find the Percentage of the hours monthly ignoring the negative time just for the time without negative
i Have The Month in Drop Down EJ 16 and i need the monthly result n Column EM 16
Am Having an issue around this, i need to find the Percentage of the hours monthly ignoring the negative time just for the time without negative
i Have The Month in Drop Down EJ 16 and i need the monthly result n Column EM 16
ILSP KPI Performance Tracking Master Data-1.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
EJ | EK | EL | EM | EN | EO | |||
16 | 01-Mar-21 | Monthly Average | ||||||
17 | Month | Mus No Vsl | VSL Arrival Jetty Date & Time | Supplier Name | Arrival Date & Time | Delay Hours | ||
18 | 01-May-21 | 1149 | 13/05/21 06:30 | Schlumberger | 13/05/21 09:10 | 2:40 | ||
19 | 01-May-21 | 1149 | 13/05/21 06:30 | Schlumberger | 13/05/21 11:10 | 4:40 | ||
20 | 01-May-21 | 1149 | 13/05/21 06:30 | Uni Arab | 13/05/21 18:30 | 12:00 | ||
21 | 01-May-21 | 1149 | 13/05/21 06:30 | Emdad | 13/05/21 17:00 | 10:30 | ||
22 | 01-May-21 | 1149 | 13/05/21 06:30 | WeatherFord | 12/05/21 16:45 | -13:45 | ||
23 | 01-May-21 | 1120 | 10/05/21 13:35 | United Safety | 09/05/21 11:00 | -2:35 | ||
24 | 01-May-21 | 1123 | 10/05/21 13:15 | Alphamed | 09/05/21 09:21 | -3:54 | ||
25 | 01-May-21 | 1125 | 10/05/21 21:15 | Halliburton | 11/05/21 10:45 | 13:30 | ||
26 | 01-May-21 | 1125 | 10/05/21 21:15 | Schlumberger | 11/05/21 09:00 | 11:45 | ||
27 | 01-May-21 | 1125 | 10/05/21 21:15 | Al Ahlia | 10/05/21 11:00 | -10:15 | ||
28 | 01-May-21 | 1125 | 10/05/21 21:15 | Alphamed | 10/05/21 09:00 | -12:15 | ||
29 | 01-May-21 | 1126 | 11/05/21 01:05 | Halliburton | 10/05/21 12:00 | -13:5 | ||
30 | 01-May-21 | 1126 | 11/05/21 01:05 | Halliburton | 09/05/21 12:20 | -12:45 | ||
31 | 01-May-21 | 1126 | 11/05/21 01:05 | Averda | 10/05/21 09:40 | -15:25 | ||
32 | 01-May-21 | 1126 | 11/05/21 01:05 | WeatherFord | 10/05/21 16:00 | -9:5 | ||
33 | 01-May-21 | 1127 | 11/05/21 07:20 | WeatherFord | 10/05/21 14:30 | -16:50 | ||
34 | 01-May-21 | 1127 | 11/05/21 07:20 | Al Ahlia | 10/05/21 12:40 | -18:40 | ||
35 | 01-May-21 | 1127 | 11/05/21 07:20 | BJ Service | 10/05/21 22:25 | -8:55 | ||
36 | 01-May-21 | 1127 | 11/05/21 07:20 | Sodexo / Kelvin | 11/05/21 16:10 | 8:50 | ||
37 | 01-May-21 | 1127 | 11/05/21 07:20 | Selective Marine | 11/05/21 20:10 | 12:50 | ||
38 | 01-May-21 | 1127 | 11/05/21 07:20 | Alphamed | 10/05/21 09:00 | -22:20 | ||
39 | 01-May-21 | 1127 | 11/05/21 07:20 | Stardford Marine | 11/05/21 20:55 | 13:35 | ||
40 | 01-May-21 | 1130 | 11/05/21 17:48 | AmBhertel LLc | 11/05/21 20:10 | 2:22 | ||
41 | 01-May-21 | 1132 | 11/05/21 15:30 | Al Ghaith | 11/05/21 09:30 | -6:0 | ||
42 | 01-May-21 | 1135 | 11/05/21 16:05 | International Tubular | 11/05/21 21:50 | 5:45 | ||
43 | 01-May-21 | 1135 | 11/05/21 16:05 | Al Ahlia | 11/05/21 16:45 | 0:40 | ||
44 | 01-May-21 | 1136 | 11/05/21 23:05 | WeatherFord | 09/05/21 16:45 | -6:20 | ||
45 | 01-May-21 | 1136 | 11/05/21 23:05 | Alphamed | 11/05/21 09:30 | -13:35 | ||
46 | 01-May-21 | 1139 | 12/05/21 01:00 | ADNH | 11/05/21 12:50 | -12:10 | ||
47 | 01-May-21 | 1137 | 11/05/21 23:30 | Al Mansoori | 10/05/21 14:00 | -9:30 | ||
48 | 01-May-21 | 1137 | 11/05/21 23:30 | Alphamed | 11/05/21 00:20 | -23:10 | ||
49 | 01-May-21 | 1137 | 11/05/21 23:30 | Halliburton | 10/05/21 13:15 | -10:15 | ||
50 | 01-May-21 | 1137 | 11/05/21 23:30 | Al Ghaith | 10/05/21 11:30 | -12:0 | ||
51 | 01-May-21 | 1137 | 11/05/21 23:30 | Scomi | 11/05/21 12:00 | -11:30 | ||
52 | 01-May-21 | 1140 | 12/05/21 09:10 | WeatherFord | 10/05/21 14:30 | -18:40 | ||
53 | 01-May-21 | 1141 | 12/05/21 11:00 | Averda | 10/05/21 09:40 | -1:20 | ||
54 | 01-May-21 | 1141 | 12/05/21 11:00 | Specialist Services | 12/05/21 13:10 | 2:10 | ||
55 | 01-May-21 | 1142 | 12/05/21 12:10 | Mi-Swaco | 09/05/21 12:10 | -0:0 | ||
56 | 01-May-21 | 1142 | 12/05/21 12:10 | Schlumberger | 12/05/21 17:50 | 5:40 | ||
57 | 01-May-21 | 1143 | 12/05/21 13:35 | ADNOC Driling / IDS | 12/05/21 08:05 | -5:30 | ||
58 | 01-May-21 | 1143 | 12/05/21 13:35 | Sodexo / Kelvin | 12/05/21 21:00 | 7:25 | ||
59 | 01-May-21 | 1147 | 12/05/21 18:25 | ADNOC Driling / IDS | 12/05/21 21:40 | 3:15 | ||
ILSP_KPI_Tracking_Data_Entry |
Cell Formulas | ||
---|---|---|
Range | Formula | |
EJ18:EJ59 | EJ18 | =IFERROR(EOMONTH(EL18,-1)+1,"") |
EL18:EL59 | EL18 | =IFERROR(VLOOKUP(EK18,$DL$18:$DW$60002,9,0),"") |
EO18:EO59 | EO18 | =IFERROR(IF(EN18-EL18<0, "-" & TEXT(ABS(EN18-EL18),"h:m"), EN18-EL18),"") |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
EJ16 | List | =$ACT$18:$ACT$209 |