Hi Friend
I need a solution which am finding slit difficult with a formula
For example in my Data Sheet
I have vessel name listed Column AI21 and i have the Departure time in column AS21
So let use a vessel as an example ADNOC-810 Departure date& Time in Column AS21 01/01/21 09:30
Return Date and Time to Jetty ADNOC-810 Column AP48 04/01/21 02:10
Now i need a help with a solution that will count the total numbers of days she spent outside before returning back to Jetty
Please i would to know how i can get this done or a better solution
I need a solution which am finding slit difficult with a formula
For example in my Data Sheet
I have vessel name listed Column AI21 and i have the Departure time in column AS21
So let use a vessel as an example ADNOC-810 Departure date& Time in Column AS21 01/01/21 09:30
Return Date and Time to Jetty ADNOC-810 Column AP48 04/01/21 02:10
Now i need a help with a solution that will count the total numbers of days she spent outside before returning back to Jetty
Please i would to know how i can get this done or a better solution
2021 Planning Supply Chain Vessel Activity and Voyage Log Sheet.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | |||
14 | Wk No's | Monthly Operation Criteria | Mus-No | Supply Vessel | D/P VSL | Vls Deck | Total Plan | FWB Date & Time | Manifest Date & Time | ADNOC Permission | Arrive Date & Time | Commence Cargo OP | Complete Cargo OP | Vessel Depart AD-Jetty | Ms't on Time YES / NO | ||
15 | 53 | 01-Jan-21 | 3705 | ADNOC-810 | P | Half | 2 | 31/12/20 21:00 | 31/12/20 11:05 | 31/12/20 21:34 | 01/01/21 00:50 | 01/01/21 03:00 | 01/01/21 09:00 | 01/01/21 09:30 | YES | ||
16 | 53 | 01-Dec-20 | 3706 | ADNOC-224 | D | Half | 5 | 31/12/20 00:45 | 31/12/20 11:30 | 31/12/20 19:09 | 31/12/20 23:00 | 01/01/21 04:00 | 01/01/21 18:25 | 01/01/21 18:48 | NO | ||
17 | 0 | 01-Jan-21 | 3666-O | ASL SWIFT | 0 | Half | 2 | 01/01/21 06:30 | 01/01/21 11:30 | 01/01/21 11:40 | 02/01/21 03:40 | Off-Hire | YES | ||||
18 | 53 | 01-Jan-21 | 3707 | Z-POWER | D | Half | 3 | 30/12/20 21:25 | 30/12/20 22:00 | 31/12/20 22:08 | 01/01/21 01:15 | 01/01/21 03:00 | 01/01/21 11:25 | 01/01/21 19:00 | NO | ||
19 | 53 | 01-Jan-21 | 3708 | SMIT LUZON | D | Full | 1 | 31/12/20 22:42 | 31/12/20 13:35 | 01/01/21 04:14 | 01/01/21 08:30 | 01/01/21 08:42 | 01/01/21 19:54 | 01/01/21 20:30 | YES | ||
20 | 53 | 01-Jan-21 | 3709 | SMIT LUMUT | D | Half | 3 | 31/12/20 22:30 | 31/12/20 18:00 | 31/12/20 23:00 | 01/01/21 05:20 | 01/01/21 07:58 | 01/01/21 17:45 | 01/01/21 19:15 | NO | ||
21 | 53 | 01-Jan-21 | 3710 | B-LIBERTY-313 | D | NAF | NAF | 01/01/21 05:30 | 31/12/20 19:30 | 01/01/21 11:30 | 01/01/21 15:09 | 02/01/21 00:00 | 02/01/21 12:05 | 02/01/21 12:45 | YES | ||
22 | 53 | 01-Jan-21 | 3711 | MAC PHOENIX | 0 | Half | 2 | 31/12/20 21:42 | 31/12/20 20:35 | 01/01/21 05:05 | 01/01/21 09:45 | 01/01/21 13:30 | 01/01/21 20:24 | 01/01/21 21:22 | NO | ||
23 | 53 | 01-Jan-21 | 3712 | ADNOC-812 | P | Half | 2 | 01/01/21 01:30 | 31/12/20 21:00 | 01/01/21 04:45 | 01/01/21 08:05 | 01/01/21 08:25 | 01/01/21 20:25 | 01/01/21 21:15 | NO | ||
24 | 53 | 01-Jan-21 | 3713 | A-HERCULES | 0 | Half | 3 | 01/01/21 04:00 | 31/12/20 21:30 | 01/01/21 09:58 | 01/01/21 13:50 | 01/01/21 15:45 | 01/01/21 23:40 | 02/01/21 00:30 | YES | ||
25 | 1 | 01-Jan-21 | 1 | LCT-TARFFAH | P | Full | 1 | 02/01/21 11:00 | 01/01/21 11:45 | 02/01/21 12:39 | 02/01/21 16:00 | 02/01/21 20:00 | 03/01/21 05:45 | 03/01/21 07:10 | YES | ||
26 | 1 | 01-Jan-21 | 2 | ADNOC-850 | P | Half | 2 | 02/01/21 20:10 | 01/01/21 13:30 | 02/01/21 20:35 | 03/01/21 00:15 | 03/01/21 03:25 | 03/01/21 12:45 | 03/01/21 13:15 | YES | ||
27 | 1 | 01-Jan-21 | 3 | ADNOC-510 | D | Full | 1 | 01/01/21 03:24 | 01/01/21 12:52 | 01/01/21 19:05 | 02/01/21 00:00 | 02/01/21 00:15 | 02/01/21 14:00 | 02/01/21 14:20 | NO | ||
28 | 1 | 01-Jan-21 | 4 | A-GRACE | D | Full | 1 | 01/01/21 05:02 | 01/01/21 13:38 | 01/01/21 17:06 | 01/01/21 21:40 | 01/01/21 23:50 | 02/01/21 05:35 | 02/01/21 06:15 | NO | ||
29 | 1 | 01-Jan-21 | 5 | MARCAP-2 | P | Full | 1 | 01/01/21 21:30 | 01/01/21 14:15 | 02/01/21 04:44 | 02/01/21 08:30 | 02/01/21 09:25 | 02/01/21 17:30 | 02/01/21 17:35 | YES | ||
30 | 1 | 01-Jan-21 | 6 | QMS DELTA | D | Half | 2 | 01/01/21 10:30 | 01/01/21 16:20 | 01/01/21 21:00 | 02/01/21 01:00 | 02/01/21 03:40 | 02/01/21 21:30 | 02/01/21 21:50 | NO | ||
31 | 1 | 01-Jan-21 | 7 | ADNOC-1011 | D | Half | 3 | 31/12/20 21:45 | 01/01/21 17:00 | 02/01/21 05:52 | 02/01/21 10:25 | 02/01/21 15:35 | 03/01/21 02:20 | 03/01/21 03:40 | NO | ||
32 | 1 | 01-Jan-21 | 8 | QMS NEPTUNE | D | Half | 3 | 01/01/21 09:50 | 01/01/21 17:20 | 01/01/21 19:14 | 02/01/21 00:15 | 02/01/21 05:20 | 02/01/21 18:10 | 02/01/21 19:00 | NO | ||
33 | 1 | 01-Jan-21 | 9 | ADNOC-221 | D | Half | 4 | 01/01/21 17:20 | 01/01/21 17:40 | 01/01/21 19:30 | 01/01/21 23:55 | 02/01/21 00:30 | 02/01/21 23:05 | 03/01/21 00:45 | NO | ||
34 | 1 | 01-Jan-21 | 10 | SEACOR ALPS | D | Full | 1 | 01/01/21 22:30 | 01/01/21 19:00 | 01/01/21 22:40 | 02/01/21 03:50 | 02/01/21 04:50 | 03/01/21 14:00 | 03/01/21 14:10 | NO | ||
35 | 1 | 01-Jan-21 | 11 | ADNOC-229 | D | NAF | NAF | 01/01/21 16:30 | 01/01/21 22:10 | 01/01/21 16:30 | 01/01/21 17:30 | 02/01/21 00:00 | 02/01/21 22:08 | 02/01/21 23:00 | NO | ||
36 | 1 | 01-Jan-21 | 12 | LCT-MARWAH-1 | P | Full | 1 | 02/01/21 05:00 | 02/01/21 09:20 | 02/01/21 09:35 | 02/01/21 13:05 | 02/01/21 15:45 | 03/01/21 00:10 | 03/01/21 00:50 | NO | ||
37 | 1 | 01-Jan-21 | 13 | Z-OCEAN | P | Full | 1 | 02/01/21 04:00 | 02/01/21 08:27 | 02/01/21 08:48 | 02/01/21 12:18 | 02/01/21 12:54 | 02/01/21 17:30 | 02/01/21 17:30 | NO | ||
38 | 1 | 01-Jan-21 | 14 | ADNOC-230 | D | NAF | NAF | 02/01/21 22:15 | 02/01/21 13:19 | 03/01/21 00:45 | 03/01/21 04:00 | 03/01/21 04:25 | 04/01/21 05:30 | 04/01/21 16:30 | YES | ||
39 | 1 | 01-Jan-21 | 15 | A-LIBERTY | D | Full | 1 | 02/01/21 12:30 | 02/01/21 13:55 | 02/01/21 18:30 | 02/01/21 22:20 | 02/01/21 22:20 | 03/01/21 04:55 | 03/01/21 06:00 | NO | ||
40 | 2 | 01-Jan-21 | 16 | ADNOC-851 | P | Full | 1 | 02/01/21 23:05 | 03/01/21 14:20 | 03/01/21 00:46 | 03/01/21 04:00 | 03/01/21 07:50 | 03/01/21 16:05 | 03/01/21 16:30 | NO | ||
41 | 1 | 01-Jan-21 | 17 | A-CHLOE | D | Full | 1 | 02/01/21 17:00 | 02/01/21 16:55 | 02/01/21 18:20 | 02/01/21 22:30 | 03/01/21 00:50 | 03/01/21 11:30 | 03/01/21 13:30 | NO | ||
42 | 1 | 01-Jan-21 | 18 | ADNOC-225 | D | NAF | NAF | 02/01/21 21:45 | 02/01/21 20:50 | 02/01/21 22:10 | 03/01/21 02:00 | 03/01/21 03:50 | 03/01/21 23:59 | 04/01/21 00:45 | NO | ||
43 | 2 | 01-Jan-21 | 19 | ADNOC-811 | P | Half | 2 | 03/01/21 02:15 | 03/01/21 08:20 | 03/01/21 08:50 | 03/01/21 12:05 | 03/01/21 12:30 | 03/01/21 19:55 | 03/01/21 21:00 | NO | ||
44 | 2 | 01-Jan-21 | 20 | SWISSCO RUBY | P | Full | 1 | 03/01/21 23:00 | 03/01/21 09:30 | 03/01/21 22:30 | 04/01/21 02:15 | 04/01/21 07:45 | 04/01/21 14:15 | 04/01/21 16:35 | YES | ||
45 | 2 | 01-Jan-21 | 21 | ADNOC-512 | D | Half | 2 | 03/01/21 14:40 | 03/01/21 10:50 | 03/01/21 11:20 | 03/01/21 18:25 | 03/01/21 19:10 | 03/01/21 21:50 | 03/01/21 22:20 | NO | ||
46 | 2 | 01-Jan-21 | 22 | AMS-RUBY | D | Half | 3 | 03/01/21 18:15 | 03/01/21 11:45 | 03/01/21 16:18 | 03/01/21 23:30 | 04/01/21 00:05 | 04/01/21 18:25 | 04/01/21 19:30 | YES | ||
47 | 2 | 01-Jan-21 | 23 | ADNOC-511 | D | Half | 4 | 03/01/21 02:50 | 03/01/21 17:18 | 03/01/21 12:46 | 03/01/21 16:25 | 03/01/21 17:18 | 03/01/21 23:25 | 03/01/21 23:50 | NO | ||
48 | 2 | 01-Jan-21 | 24 | ADNOC-810 | P | Half | 3 | 03/01/21 22:15 | 03/01/21 11:30 | 03/01/21 18:15 | 04/01/21 02:10 | 04/01/21 02:35 | 04/01/21 07:30 | 04/01/21 16:20 | YES | ||
49 | 2 | 01-Jan-21 | 25 | ADNOC-812 | P | Half | 2 | 03/01/21 21:00 | 03/01/21 11:50 | 03/01/21 21:30 | 04/01/21 00:15 | 04/01/21 00:30 | 04/01/21 05:10 | 04/01/21 16:20 | YES | ||
ILSP Vessel Activity Tracking |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AF15:AF49 | AF15 | =IFERROR(WEEKNUM([@[Manifest Date & Time]]),"") |
AG15:AG49 | AG15 | =IFERROR(EOMONTH([@[Arrive Date & Time]],-1)+1,"") |
AJ15:AJ49 | AJ15 | =IFERROR(IF(VLOOKUP([@[Supply Vessel]],Vessels,1,0)=[@[Supply Vessel]],VLOOKUP([@[Supply Vessel]],Vessels,2,0),NA()),"") |
AK15:AK49 | AK15 | =IFERROR(IF(AL15=UniqueList!$P$2,"M",IF(AL15=UniqueList!$P$3,"NAF",IF(AL15=""," ",IF(AL15>1,"Half",IF(AL15<1,"Half","Full "))))),"") |
AT15:AT49 | AT15 | =IFERROR(IF(AN15>AM15-TIME(6,0,0),"NO","YES"),"") |