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

 

[etaf]

so all you want is the Arrival time based on
Vessel letter A,B or C
Add 6 days

lets take Vessel A - it departs on a Sun or Wed
do you have the Date the Vessel departs ?
if so then all you need to do is ADD 6 to that date

Or are you trying to Guess the departure date ? based on the day of week and Excel nowing what today is when you open the spreadsheet

Not sure what point 2 is involved in the calculation

can you add some manual entries
So data you know and the expected result

I'm sure just adding 6 days to the departure date is NOT what you are looking for

 

[hakim_tanzil]

so all you want is the Arrival time based on
Vessel letter A,B or C
Add 6 days

lets take Vessel A - it departs on a Sun or Wed
do you have the Date the Vessel departs ?
if so then all you need to do is ADD 6 to that date

Or are you trying to Guess the departure date ? based on the day of week and Excel nowing what today is when you open the spreadsheet

Not sure what point 2 is involved in the calculation

can you add some manual entries
So data you know and the expected result

I'm sure just adding 6 days to the departure date is NOT what you are looking for

Hi Wayne,

Many thanks for your reply and sorry for the lack of information

The point 2 above means:
1. Assuming Vessel A departs from the ship terminal every Sunday or Thursday
2. If container is sent to the port on Thursday, then it will be load onto the Sunday departure Vessel
3. If it is sent to the port on Friday (or Saturday), then the container can't be load onto the Sunday departure, it will be loaded onto the next Thursday departure

So the manual entry is the date when the container is sent out from our supplier warehouse (and received at the terminal port). If everything are on schedule then it will be easy enough for me to just add 6 to the date, but most of the times (especially these days), almost everything are late

 

[Toadstool]

Hi Hakim_tanzil,

You don't explain the relationship between "Loading Day" (the date you are given) and "Departure Day" or "Closing Day"?
I'm guessing the Loading Day is the completion of loading and therefore the same day you advise the terminal it is ready for departure, i.e. Loading Day = Closing Day.

It would seem you want the next day of the week for a Vessel departure which is 3 days after the Loading Day. Correct?
e.g. If the Loading Day for a Vessel type A is 13 September 2021 (a Monday) then that is also the Closing Day so 3 days beyond that is 16 September 2021 which is a Thursday, so too late for a Vessel A Wednesday departure so you want the following Sunday, which would be 19 September 2021. With 6 days travel you would want that Loading Day for a Vessel type A of 13 September 2021 to give an ETA for 25 September 2021. Correct?

 

[hakim_tanzil]

Hi Hakim_tanzil,

You don't explain the relationship between "Loading Day" (the date you are given) and "Departure Day" or "Closing Day"?
I'm guessing the Loading Day is the completion of loading and therefore the same day you advise the terminal it is ready for departure, i.e. Loading Day = Closing Day.

It would seem you want the next day of the week for a Vessel departure which is 3 days after the Loading Day. Correct?
e.g. If the Loading Day for a Vessel type A is 13 September 2021 (a Monday) then that is also the Closing Day so 3 days beyond that is 16 September 2021 which is a Thursday, so too late for a Vessel A Wednesday departure so you want the following Sunday, which would be 19 September 2021. With 6 days travel you would want that Loading Day for a Vessel type A of 13 September 2021 to give an ETA for 25 September 2021. Correct?

Hi Toadstool,

Thank you for your reply, and again sorry for the misinformation

What I meant by "Loading Day" is the day the container is received at the terminal port

eg:
1. If the containers are arrived at the port on 13 Sept 2021 (Mon), then it will be loaded onto the 16 Sept 2021 (Thurs) vessel. And will be arrived at our warehouse in about 6 days which is around 21 September 2021 (Tue)
2. If the containers are sent to the port on 14 Sept 2021 (Tue), then it will be loaded onto the 19 Sept 2021 (Sun) vessel. It will arrive at our warehouse on around 24 September 2021 (Sat)

 

[etaf]

would you

can you add some manual entries
So data you know and the expected result

with real dates
Show what your actual data you input into excel and then the expected answer and why
if things are delayed

but most of the times (especially these days), almost everything are late

Do you type in the Arrival date as posted

1. If the containers are arrived at the port on 13 Sept 2021 (Mon), then it will be loaded onto the 16 Sept 2021 (Thurs) vessel. And will be arrived at our warehouse in about 6 days which is around 21 September 2021 (Tue)

If so then we have the vessel letter
so if this was a

Vessel A: Wed or Sun, Vessel B: Mon or Thur, Vessel C: Mon or Wed, etc)

Vessel B, then arriving on the Mon 13th Sept, we now it cannot leave now until Thursday , BUT given there is a three day wait - then thats MON, TUES, WED , so it can go on the Thursday - if it arrived anytime up to Friday , then it can go out on the Monday run ,
So 6 days from Thur or 6 days from SUN depending on when it arrives

If in your example it was Vessel A , Wed/Sun - then arriving on Mon 13th Sept, would be to late for the Wed sail, and would have to wait until the Sunday Sail

AND IF it was Vessel C arriving on the Mon 13th - again tolate for Wed, so would leave the following monday

Not sure I understand the delays part

but is the above example for Arriving on Monday 13th Sept - for vessel B, A, C correct

vessel A Arrives on 13th Sept Mon - departs on Sunday 19th - arrives 25th Sat
vessel B arrives on 13th Sept mon - departs on Thursday 16th - arrives 22nd wed
vessel C arrives on 13th Sept mon - departs on Monday 20th - arrives 26th sun
are those conditions correct

any more examples ?

 

[etaf]

in Addition to above this is getting quite complicated in creating a solution, at least for me based on Vessel and days of week etc - So do need to know precisely, number of days etc - if three days includes day arriving and day leaving OR if need 3 absolute days between. also I'm assuming all 7days a week apply
you also said etc

(eg: Vessel A: Wed or Sun, Vessel B: Mon or Thur, Vessel C: Mon or Wed, etc)

So how many vessels are there and do they ALL have 2 days of sailing ?
I'm using a lookup table, so this could be quite important if you have lots of vessels and they have 1,2,3,4 different sailing days
Although I'm not near a solution yet

 

[Toadstool]

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)

 

[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)

This seems work. Many thanks to everyone reply

 

[Toadstool]

You're welcome and thanks for letting us know.