Hi Friends
Good day to you all and I hope everyone is having a great weekend, Please i would appreciate it if anyone could help out with a formula that will count the vessel turnaround trip weekly basis per each vessel and the average for the full month since my boss came up with something new...
my data Entry is as seen, in this case, I will explain and give an example of what I meant and I would like to have the result to be.
Example of Vessel Turnaround. So let us use Column D I have the is (MUS No: 3535), which is the first voyage for ADNOC-230 In Column E, let's say now in Column O is the departure date & time 01/12/2023 at 18:30:00
to complete this voyage she needs to return to the Port to complete that MUS Voyage and her return date is 03/12/2023 at 22:30:00 this will be counted as one Trip and when she goes again and back again this will be counted a two Trip so I need help with a formula to get this counted for each trip weekly in Column V-4, W, X, Y, Z based on criteria vessel name.
So I have the list of Vessel Name Fix in Column T 4, and I have my Month validated in Column U 2
In Column C, I have my Week Number
Also in Column P, I need to have the return date and in Column -Q, will be the total days she spent outside before returning to Port. I also placed an Example of what i need all formula to work out for me.
Appreciate any help and support from all my friends
Regards
Good day to you all and I hope everyone is having a great weekend, Please i would appreciate it if anyone could help out with a formula that will count the vessel turnaround trip weekly basis per each vessel and the average for the full month since my boss came up with something new...
my data Entry is as seen, in this case, I will explain and give an example of what I meant and I would like to have the result to be.
Example of Vessel Turnaround. So let us use Column D I have the is (MUS No: 3535), which is the first voyage for ADNOC-230 In Column E, let's say now in Column O is the departure date & time 01/12/2023 at 18:30:00
to complete this voyage she needs to return to the Port to complete that MUS Voyage and her return date is 03/12/2023 at 22:30:00 this will be counted as one Trip and when she goes again and back again this will be counted a two Trip so I need help with a formula to get this counted for each trip weekly in Column V-4, W, X, Y, Z based on criteria vessel name.
So I have the list of Vessel Name Fix in Column T 4, and I have my Month validated in Column U 2
In Column C, I have my Week Number
Also in Column P, I need to have the return date and in Column -Q, will be the total days she spent outside before returning to Port. I also placed an Example of what i need all formula to work out for me.
Appreciate any help and support from all my friends
Regards
| 2023 - VESSELS ONSHORE JETTY OPERATIONS.xlsx | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
| 2 | Month Filter >>> | December 2021 | ||||||||||||||||||||||||||
| 3 | Month | WK NUM | Mus No | Supply Vessel | Vessel Arrived FWB Date&Time | Manifest Date & Time | Control Tower Permission Date & Time | Berth Position Date & Time | Berth Nos | Vessel Arrive Secured Berth Date & Time | Berth Nos | Start Cargo Deck offload&Load | Complete Date & Time | Departure Adnoc Port Date & Time | Vessel Return Date & Time | Total Spent Days | Vessels Name | Total Nos Of Trips | Wk-1 | Wk-2 | Wk-3 | Wk-4 | Wk-5 | Average Trip | ||||
| 4 | Dec-23 | 1 | 3532 | MUTAWA-304 | 29/11/23 01:00 | 30/11/23 10:50 | 30/11/23 19:00 | 01/12/23 00:30 | 01/12/23 00:30 | B10-1 | 01/12/23 00:55 | 01/12/23 04:30 | 01/12/23 13:15 | ADNOC-1010 | ||||||||||||||
| 5 | Nov-23 | 5 | 3533 | LCT-TARFFAH-1 | 29/11/23 10:30 | 30/11/23 11:35 | 30/11/23 11:45 | 30/11/23 15:45 | B3-2 | 30/11/23 19:20 | B3-1 | 30/11/23 20:00 | 01/12/23 00:10 | 01/12/23 01:45 | ADNOC-1011 | |||||||||||||
| 6 | Dec-23 | 1 | 3534 | SWISSCO RUBY | 29/11/23 01:50 | 30/11/23 13:20 | 29/11/23 22:20 | 01/12/23 02:00 | 01/12/23 02:00 | B6-1 | 01/12/23 02:55 | 01/12/23 16:30 | 01/12/23 17:30 | ADNOC-221 | ||||||||||||||
| 7 | Dec-23 | 1 | 3535 | ADNOC-230 | 30/11/23 14:30 | 30/11/23 16:33 | 30/11/23 19:25 | 01/12/23 00:20 | 01/12/23 00:20 | B1-1 | 01/12/23 00:55 | 01/12/23 17:00 | 01/12/23 18:30 | 03/12/23 22:30 | 2.2 | ADNOC-222 | ||||||||||||
| 8 | Dec-23 | 1 | 3536 | WARDEH-1 | 30/11/23 19:20 | 30/11/23 19:15 | 01/12/23 03:40 | 01/12/23 07:10 | 01/12/23 07:10 | B11-1 | 01/12/23 08:30 | 01/12/23 21:10 | 01/12/23 22:00 | ADNOC-223 | ||||||||||||||
| 9 | Nov-23 | 5 | 3537 | LCT RAZAN-1 | 29/11/23 01:50 | 30/11/23 20:19 | 29/11/23 02:20 | 30/11/23 22:45 | 30/11/23 22:45 | B8-1 | 30/11/23 23:40 | 01/12/23 12:45 | 01/12/23 13:10 | ADNOC-224 | ||||||||||||||
| 10 | Dec-23 | 1 | 3538 | STANFORD ALPHA | 29/11/23 05:00 | 30/11/23 22:44 | 01/12/23 01:55 | 01/12/23 03:15 | 01/12/23 03:15 | B9-1 | 01/12/23 04:48 | 01/12/23 22:40 | 01/12/23 23:05 | ADNOC-225 | ||||||||||||||
| 11 | Dec-23 | 1 | 3539 | JOPETWIL-62 | 30/12/23 20:00 | 30/11/23 22:37 | 01/12/23 01:52 | 01/12/23 06:30 | 01/12/23 06:30 | B3-1 | 01/12/23 06:30 | 01/12/23 15:55 | 01/12/23 17:00 | ADNOC-226 | ||||||||||||||
| 12 | Dec-23 | 1 | 3540 | CECILIE-K | 01/12/23 12:24 | 01/12/23 15:55 | 01/12/23 16:25 | 01/12/23 23:12 | 01/12/23 23:12 | B14-1 | 01/12/23 23:54 | 02/12/23 12:24 | 02/12/23 12:48 | ADNOC-227 | ||||||||||||||
| 13 | Dec-23 | 1 | 3541 | AD-ASTRA | 01/12/23 18:00 | 01/12/23 16:30 | 01/12/23 16:52 | 01/12/23 23:25 | 01/12/23 23:25 | RORO | 01/12/23 23:35 | 01/12/23 23:47 | 01/12/23 23:59 | ADNOC-228 | ||||||||||||||
| 14 | Dec-23 | 1 | 3542 | LCT BIMA | 30/11/23 21:20 | 01/12/23 17:50 | 01/12/23 18:00 | 01/12/23 19:30 | 01/12/23 19:30 | 01/12/23 20:45 | 02/12/23 09:30 | 02/12/23 10:00 | ADNOC-229 | |||||||||||||||
| 15 | Dec-23 | 1 | 3543 | ADNOC-224 | 02/12/23 04:30 | 01/12/23 19:45 | 02/12/23 04:30 | 02/12/23 08:35 | 02/12/23 08:35 | B3-1 | 02/12/23 09:00 | 02/12/23 16:10 | 02/12/23 16:40 | ADNOC-230 | ||||||||||||||
| 16 | Dec-23 | 1 | 3544 | LCT BUSHRA | 02/12/23 12:37 | 01/12/23 21:30 | 01/12/23 22:38 | 02/12/23 17:36 | 02/12/23 17:36 | B10-1 | 02/12/23 18:50 | 03/12/23 00:35 | 03/12/23 00:55 | ADNOC-510 | ||||||||||||||
| 17 | Dec-23 | 1 | 3545 | VENTURE | 02/12/23 00:15 | 01/12/23 22:35 | 01/12/23 22:38 | 02/12/23 04:25 | 02/12/23 04:25 | B6-1 | 02/12/23 04:40 | 02/12/23 08:55 | 02/12/23 09:30 | ADNOC-511 | ||||||||||||||
| 18 | Dec-23 | 1 | 3546 | A-CHLOE | 02/12/23 02:30 | 02/12/23 00:40 | 02/12/23 01:00 | 02/12/23 06:15 | 02/12/23 06:15 | B9-1 | 02/12/23 07:00 | 02/12/23 23:55 | 03/12/23 00:45 | ADNOC-512 | ||||||||||||||
| 19 | Dec-23 | 1 | 3547 | ADNOC-1010 | 02/12/23 07:00 | 02/12/23 02:00 | 02/12/23 04:30 | 02/12/23 11:15 | 02/12/23 11:15 | B8-1 | 02/12/23 11:35 | 02/12/23 20:45 | 02/12/23 21:35 | ADNOC-810 | ||||||||||||||
| 20 | Dec-23 | 1 | 3548 | ADNOC-221 | 02/12/23 15:30 | 02/12/23 11:19 | 02/12/23 11:25 | 02/12/23 20:05 | 02/12/23 20:05 | B5-1 | 02/12/23 20:30 | 03/12/23 08:20 | 03/12/23 09:00 | ADNOC-811 | ||||||||||||||
| 21 | Dec-23 | 1 | 3549 | LCT MARWAH-1 | 02/12/23 14:00 | 02/12/23 13:30 | 02/12/23 13:33 | 02/12/23 18:15 | 02/12/23 18:15 | B4-1 | 02/12/23 19:15 | 03/12/23 01:05 | 03/12/23 02:45 | ADNOC-812 | ||||||||||||||
| 22 | Dec-23 | 1 | 3550 | ADNOC-226 | 02/12/23 16:55 | 02/12/23 17:30 | 02/12/23 05:40 | 02/12/23 21:15 | 02/12/23 21:15 | B1-1 | 02/12/23 21:45 | 03/12/23 20:30 | 03/12/23 21:40 | ADNOC-850 | ||||||||||||||
| 23 | Dec-23 | 1 | 3551 | ADNOC-229 | 02/12/23 20:50 | 02/12/23 18:30 | 02/12/23 18:37 | 03/12/23 01:00 | 03/12/23 01:00 | B6-1 | 03/12/23 02:55 | 04/12/23 13:30 | 04/12/23 18:25 | ADNOC-851 | ||||||||||||||
| 24 | Dec-23 | 1 | 3552 | JOPETWIL-68 | 02/12/23 10:30 | 02/12/23 17:22 | 02/12/23 12:00 | 02/12/23 22:55 | 02/12/23 22:55 | B12-1 | 02/12/23 22:55 | 03/12/23 22:00 | 03/12/23 22:00 | ADNOC-A02 | ||||||||||||||
| 25 | Dec-23 | 1 | 3553 | A-GRACE | 02/12/23 18:00 | 02/12/23 20:00 | 02/12/23 20:40 | 03/12/23 01:20 | 03/12/23 01:20 | B13-1 | 03/12/23 02:25 | 03/12/23 12:25 | 03/12/23 12:45 | ADNOC-A03 | ||||||||||||||
| 26 | Dec-23 | 1 | 3554 | ADNOC-A05 | 02/12/23 19:50 | 02/12/23 21:20 | 02/12/23 21:51 | 03/12/23 01:45 | 03/12/23 01:45 | B14-1 | 03/12/23 02:25 | 03/12/23 20:30 | 03/12/23 21:25 | ADNOC-A04 | ||||||||||||||
| 27 | Dec-23 | 1 | 3555 | LCT-TARFFAH-1 | 03/12/23 10:00 | 02/12/23 21:50 | 03/12/23 06:55 | 03/12/23 23:15 | 03/12/23 23:15 | B3-1 | 03/12/23 23:45 | 04/12/23 13:40 | 04/12/23 14:15 | ADNOC-A05 | ||||||||||||||
| 28 | Dec-23 | 1 | 3556 | M-SUPPORTER | 03/12/23 02:10 | 03/12/23 00:50 | 03/12/23 01:50 | 03/12/23 06:35 | 03/12/23 06:35 | 03/12/23 12:55 | 03/12/23 20:30 | 03/12/23 20:50 | ADNOC-S01 | |||||||||||||||
| 29 | Dec-23 | 1 | 3557 | LCT-JEWAHER-1 | 03/12/23 00:10 | 03/12/23 02:00 | 03/12/23 02:10 | 03/12/23 05:55 | 03/12/23 05:55 | B7-1 | 03/12/23 07:35 | 04/12/23 11:00 | 04/12/23 11:40 | ADNOC-S02 | ||||||||||||||
| 30 | Dec-23 | 1 | 3558 | ADNOC-511 | 03/12/23 00:30 | 03/12/23 08:48 | 03/12/23 08:51 | 03/12/23 18:10 | 03/12/23 18:10 | B5-1 | 03/12/23 19:30 | 03/12/23 21:15 | 03/12/23 22:00 | A-CHLOE | ||||||||||||||
| 31 | Dec-23 | 1 | 3559 | CREST RADIANT-5 | 03/12/23 05:00 | 03/12/23 09:55 | 03/12/23 10:02 | 03/12/23 15:30 | 03/12/23 15:30 | B2-1 | 03/12/23 16:05 | 03/12/23 20:58 | 04/12/23 08:40 | A-GRACE | ||||||||||||||
| 32 | Dec-23 | 1 | 3560 | WARDEH-1 | 03/12/23 11:00 | 03/12/23 11:15 | 03/12/23 22:35 | 03/12/23 22:35 | B12-1 | 03/12/23 23:20 | 04/12/23 11:20 | 04/12/23 12:55 | A-LIBERTY | |||||||||||||||
| 33 | Dec-23 | 1 | 3561 | Z-OCEAN | 03/12/23 18:00 | 03/12/23 13:17 | 03/12/23 13:16 | 03/12/23 21:00 | 03/12/23 21:00 | B13-1 | 03/12/23 22:18 | 04/12/23 01:45 | 04/12/23 02:07 | AMS-ONYX | ||||||||||||||
| 34 | Dec-23 | 1 | 3562 | ADNOC-230 | 03/12/23 15:00 | 03/12/23 17:50 | 03/12/23 18:30 | 03/12/23 22:30 | 03/12/23 22:30 | B1-1 | 03/12/23 23:05 | 04/12/23 23:40 | 05/12/23 00:15 | AMS-RUBY | ||||||||||||||
2023-Vessel Onshore Operations | ||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B34 | B4 | =IFERROR(EOMONTH(K4,-1)+1,"") |
| C4:C34 | C4 | =IFERROR(MATCH(DAY(K4),{1,8,15,22,29}),"") |
| Q7 | Q7 | =P7-O7 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| U2 | List | =Info_Settings!$B$3:$B$99 |
| X2 | List | =Info_Settings!$B$3:$B$16 |
