Eta crossing the international time line

This was a question that was posted to me. When do we need to leave Saipan local time(gmt-10) to arrive at the Panama canal on May 1st 2024 1800 local time (gmt+5).
The speed would be 12 knots and the total mileage would be 7,979 nautical miles.
Does anyone have an Excel spreadsheet for this kind of question?
It’s a reverse ETA question involving international date line and multiple time zones.

1 Like

Spreadsheet? Just convert everything to GMT and do the math. Then convert back to local.


4/4 @ 1600


There are probably more than one way to look approach this. From an engineer’s viewpoint, if it is 1800 local, May 1st at the Panama Canal it is 0900 local, May 2nd in Saipan. The journey will take (7979/12) 664.91667 hours, or 27 days., 16 hours, 55 minutes. Count back from 0900, May 2nd.

I come up with 4/4 @ 1605.


When I was in trade management with a Scandinavian owner we only used GMT, departures arrivals everything. Port agents, some we owned some independent, did the conversion to local as required. Example Panama agents booked our transits based on the Masters GMT ETA. Can’t say sure but don’t know of any shipowner that does different


You’re using a confusing way to describe the time differance. It looks like you’re using the civilian method which is shown as a correction from GMT, so Saipan would be (GMT + 10)

Navigators use the Zone Description (ZD) which corrects to GMT so Saipan is in ZD (-10)

To solve the problem, unless I was pressed for time I would use a spreadsheet. It might take an extra minute or two but less chance of making an error but the big advantage is the values can be easily changed without having to redo the problem.

I learned Excel by using the “Help” function, it’s very useful.


ETA.xlsx (10.3 KB)

Never heard it called Zone Description before.

In the Wikipedia article on Time Zones on the map the zones are labeled positive east of Greenwich and negative west of Greenwich.

However in Bowditch the time zone chart is the opposite. So Panama (like Maine now) is (UTC-5) but it’s ZD (+5).

In both cases the sign is flipped when the conversion is the other way.


This was what I was looking for. I have been working with this formula, but have noticed that the start time is still a day off. Wouldn’t the day of starting be 1 day later due to the international date line?
I swear they should start teaching excel in school these days.

No, you don’t have to worry about that because your start and end times are all in UTC.

If short voyage is made just across the date lines it’s ZD (-12) and ZD(+12) a conversion local to UTC and back to local the date is handled. (the time stays the same, only the date changes)

If the Saipan - Panama voyage was made westbound the date line would not be crossed but converting to UTC still works.

Agree normalizing all times to UTC is the One True Way, but it looks like the May 1st date was mentioned to be in Panama time, not Saipan time, so I’d swap the ZD values in the spreadsheet.

1 Like

The problem was worked backwards to find ETD. This is the same problem worked to find ETA.

Here’s the same spreadsheet crossing the dateline:

Ops, yes you’re right.

Two wrongs sometimes do make a right.

Here’s a corrected spreadsheet.
ETA.xlsx (10.3 KB)

Edit: Here’s another way of doing it which maybe is simpler. It’s coming up on noon time Saturday in Panama now and in Saipan it’s coming up on 0300 hrs Sunday. So if we subtract 15 hours from Saipan time we’ll get Panama time:

All these complicated spreadsheets when it’s literally faster to do it by hand than it is to make sure the formulas are correct :joy:

1 Like

Thirty days has September, all the rest I can’t remember.

Really only one spreadsheet. The formula is simple, some variation of this: =I2-j2/24 it adds hours to a time and date.

The OP does ask for a spreadsheet.

Here’s a better screen shot of the spreadsheet

Here’s the formulas.

Cells E2, G2, I2 and K2 are all formatted as date. In the sheet cell E2 shows the end (arrival) time the way Excel uses them but was entered as shown in the formula bar.


The formulas here are simple, the trick is to get the signs correct. Using Zone Descriptions (ZD) to convert from zone time (ZT) to UTC the ZD is added. When converting from UTC to zone time ZD is subtracted.

So how does the spread sheet factor arrival port daylight savings time. Practiced widely different around the word. Some even half hour differences

Report ETA in GMT local agents make the calculation. Errors are charged to the vessel. Pilots Tugs Line Handling Stevedore all ordered on local time by the agent. Agents in the best position to convert times,they do it everyday.

I never heard about ZD (Zone Description).
It is really not hard to transform the GMT Zones into ZD… but just because it is very easy, it is prone to oversight errors.

Let Excel do the transformation, by entering the common UTC time zone and multiplying it by -1 to have ZD.
In cell G2 this would be >>> =E2+ F2*-1/24

ISO 12.2.8

12.2.8 zone description Time difference between zone time and UTC

Strictly speaking zone description is not necessarily being used. I used it here to indicate what sign to use as opposed to the (GMT +/-) offset. Zones in maritime are fixed geographical areas but of course in practice they don’t always match what’s on the ground.

Unlike actual ZDs It’s not required to be even hours. The spreadsheet is actually adding days so 1 hour is 1/24 so half an hour would be 0.5/24 etc.

Ok, so here’s what an offset is according to ISO 8601

Negative UTC offsets describe a time zone west of UTC±00:00,

According to Bowditch this is a Zone Description:

Each time zone is identified by the number of times the longitude of its zone meridian is divisible by 15°, positive in west longitude and negative in east longitude

Offsets are negative west of UTC and ZD are positive west of GMT

This is from the OP:

When do we need to leave Saipan local time(gmt-10) to arrive at the Panama canal on May 1st 2024 1800 local time (gmt+5).

Not 100% clear but most likely the OP is using ZD not offsets. .To keep it simple I used ZD in the spreadsheet assuming that was what was asked for.

Simple spreadsheets like this are easy to create or edit. I just use what makes the most sense to me.

It makes sense that if the office is in a different time zone GMT would be used. However the office uses local times to communicate locally and so does the ship.

Typically the ship’s clocks will be changed to match local time for convenience and to coordinate in port with pilots, longshoreman, agents, and so forth.

Determining the local time of the arrival port is done as part of the voyage planning process. Schedules received from the agent will be in local times, pre-arrival notifications are usually required to be in local times, the AIS is set local etc.