Calculating estimation time of arrival

[hakim_tanzil]

Hi there,

Recently, I've been assigned to handle the tasks for the ship containers, in which I need a formula to calculate the estimation of the time arrival (ETA) based on the loading day

Here are some of my understanding that might help:
1. Every vessel has their own specific departure days (eg: Vessel A: Wed or Sun, Vessel B: Mon or Thur, Vessel C: Mon or Wed, etc)
2. The last day (or the so called closing day) for the terminal to accept all the containers is about 3 days prior departure (eg: if depart on Sun, the last day for the containers to be accepted at the terminal has to be on Thursday)
3. Let's assume once the vessel departed, it will take about 6 days for the containers to arrive at my warehouse (this is the date that I'm looking for)

Hope the explanation above can assist you all.

Any inputs would be greatly appreciated

 

[hakim_tanzil]

Hi Toadstool,

The timetable should be like this (I'll just focus on Vessel A):

Vessel​	Arrived at Port​	Depart​	ETA​
A​	13-Sep-2021 (Mon)​	16-Sep-2021 (Thu)​	21-Sep-2021 (Tue)​
A​	16-Sep-2021 (Thu)​	19-Sep-2021 (Sun)​	24-Sep-2021 (Fri)​
A​	17-Sep-2021 (Fri)​	23-Sep-2021 (Thu)​	28-Sep-2021 (Tue)​
A​	21-Sep-2021 (Tue) ​	26-Sep-2021 (Sun)​ ​	01-Oct-2021 (Fri)​

It looks like the formula suggested earlier does work with the Vessel table, but when I tried this formula:

=IF(C2="","",WORKDAY.INTL(C2-1,1,INDEX(Vessel!$I$2:$I$999,MATCH(A2,Vessel!$A$2:$A$999,0))))

It still didn't get me the right ETA date

 

[hakim_tanzil]

Hi Hakim_tanzil,

I may have a solution for you. Below are two worksheets:

1. Vessel - Here you have one row for each vessel and in the Mon to Sun cells put any character for the day which the vessel can sail. Column I is creating a string of seven values, "1" for any day where it can't sail and "0" for a day it can sail.
2. Timetable - Here is where you enter the Vessel and Arrived at Port date. My headings probably don't match with your names but I got very confused interpreting the requirement so I've made my own names and have more columns than you actually need but I wanted to be clear for testing.

Let me explain my Ready, Loaded and ETA dates but note they're all prefixed by an IF so they only populate if there is a Vessel and Arrived date entered:

• Ready: I understood it takes 3 days from Arrival before it could depart the port so this is just Arrived + 3
• Loaded: The NETWORKDAYS.INTL is used to calculate a date based on a start dates and number of days to be added but it uses a parameter, which can be a string of seven digits like "1110110", to say which days can be worked. We can take the Ready Date (and subtract 1 so if Ready and Ship Day are the same we can get the same date) and a fixed number of days of 1 because we just want the next available ship date. The INDEX/MATCH retrieves the correct 7 digit string from the Vessel sheet.
• ETA: Just adds 5 to the Loaded date as it should arrive on the sixth day.

Please do some testing and let me know if this works for you.

Hakim_tanzil.xlsx
	A	B	C	D	E	F	G	H	I
1	Vessel	Mon	Tues	Wed	Thu	Fri	Sat	Sun	Ship Days
2	A				y			y	1110110
3	B	y			y				0110111
4	C	y		y					0101111
5
Vessel

Cell Formulas
Range	Formula
I2:I4	I2	=SUBSTITUTE(SUBSTITUTE(NOT(B2<>"")&NOT(C2<>"")&NOT(D2<>"")&NOT(E2<>"")&NOT(F2<>"")&NOT(G2<>"")&NOT(H2<>""),"TRUE","1"),"FALSE","0")

Hakim_tanzil.xlsx
	A	B	C	D	E
1	Vessel	Arrived	Ready	Loaded	ETA
2	A	13-Sep-21	16-Sep-21	16-Sep-21	21-Sep-21
3	A	14-Sep-21	17-Sep-21	19-Sep-21	24-Sep-21
4	B	15-Sep-21	18-Sep-21	20-Sep-21	25-Sep-21
5	C	12-Sep-21	15-Sep-21	15-Sep-21	20-Sep-21
6
Timetable

Cell Formulas
Range	Formula
C2:C6	C2	=IF(OR(A2="",B2=""),"",B2+3)
D2:D6	D2	=IF(C2="","",WORKDAY.INTL(C2-1,1,INDEX(Vessel!$I$2:$I$999,MATCH(A2,Vessel!$A$2:$A$999,0))))
E2:E6	E2	=IF(D2="","",D2+5)

Hi Toadstool,

The timetable should be simplified to (I only focus on vessel A):

Vessel	Arrived at Port	Depart	ETA
A	13-Sep-21	16-Sep-21	21-Sep-21
A	14-Sep-21	19-Sep-21	24-Sep-21
A	16-Sep-21	19-Sep-21	24-Sep-21
A	18-Sep-21	23-Sep-21	28-Sep-21

I've tried the formula you suggested earlier + the Vessel table

=IF(C2="","",WORKDAY.INTL(C2-1,1,INDEX(Vessel!$I$2:$I$999,MATCH(A2,Vessel!$A$2:$A$999,0))))

The ETA that I get is correct when the "Arrived at Port" date is on either Mon or Thu. Otherwise the result is all wrong

 

[Toadstool]

Hi Hakim,

It works for me but I see you've removed a column and I can't tell which columns refer in your formula. I have therefore removed my work column to match your desired columns.
I have formatted the date cells as Custom with a type dd-mmm-yy (ddd) so it matches your format.

Here it is with those changes:

Hakim_tanzil-2.xlsx
	A	B	C	D	E	F	G	H	I
1	Vessel	Mon	Tues	Wed	Thu	Fri	Sat	Sun	Ship Days
2	A				y			y	1110110
3	B	y			y				0110111
4	C	y		y					0101111
Vessel

Cell Formulas
Range	Formula
I2:I4	I2	=SUBSTITUTE(SUBSTITUTE(NOT(B2<>"")&NOT(C2<>"")&NOT(D2<>"")&NOT(E2<>"")&NOT(F2<>"")&NOT(G2<>"")&NOT(H2<>""),"TRUE","1"),"FALSE","0")

Hakim_tanzil-2.xlsx
	A	B	C	D
1	Vessel	Arrived at Port	Depart	ETA
2	A	13-Sep-21 (Mon)	16-Sep-21 (Thu)	21-Sep-21 (Tue)
3	A	16-Sep-21 (Thu)	19-Sep-21 (Sun)	24-Sep-21 (Fri)
4	A	17-Sep-21 (Fri)	23-Sep-21 (Thu)	28-Sep-21 (Tue)
5	A	21-Sep-21 (Tue)	26-Sep-21 (Sun)	01-Oct-21 (Fri)
6
Timetable

Cell Formulas
Range	Formula
C2:C6	C2	=IF(OR(A2="",B2=""),"",WORKDAY.INTL(B2+2,1,INDEX(Vessel!$I$2:$I$999,MATCH(A2,Vessel!$A$2:$A$999,0))))
D2:D6	D2	=IF(C2="","",C2+5)