Sailing Passage Tracking Spreadsheet

THIS POST MAY CONTAIN AFFILIATE LINKS. PLEASE READ OUR DISCLOSURE FOR MORE INFO.

Last Updated on February 12, 2022 by David

---

There aren’t many times where one of my loves of my past life (spreadsheets) combines with sailing. But coming up with a solution for how to track our performance under passage was the perfect opportunity!

I created this spreadsheet as a way to help plan our passages, track our performance underway and help us make the decision whether to go all out (aka use the big headsails or motorsail) or reef and slow down to make sure we arrive at our destination during daylight hours. It’s evolved over the years, but I’m hopeful it has gotten to a place where people will find it helpful, and not too complicated!

Be sure to download the spreadsheet with the link above. If you end up using the spreadsheet while you’re on passage, be sure to let us know how it goes! We’d also love to hear if you have any suggestions for improvement!

There are some basic instructions included in the spreadsheet itself (see the sheets at the bottom of the window), and there are more detailed ones below. But the best way to get familiar with the spreadsheet and how we’ve used it is to watch our video:

Basic Spreadsheet Information

• Blue Cells: Any cell/column that are highlighted in Blue require a manual entry. Everything else is automatically calculated.
• Dates & Times: An American made this spreadsheet, so the dates are formatted “mm/dd/yyyy”. Times are formatted using 24 hour notation. So 4PM = 16:00. PRO TIP: Edit dates and times in the Formula Input Line rather than trying to type them in the cell directly. This helps to keep formatting consistent
• Current Time: The last date/time value in Column A is what the spreadsheet will consider the ‘Current Time’. This is used for all the ETA calculations so make sure it is updated appropriately.

Passage Planning

Before we depart on any passage, I use this spreadsheet to give me a rough idea of how long a passage will take. Trying to plan a passage so we arrive during daylight hours at our destination is always a top priority for us. I play around with our departure time in this spreadsheet to give ourselves the best possible chance of arriving when we want, at a pace we can easily sail.

Here’s how to reset the spreadsheet so you can start planning from scratch:

Step 1: Remove all the old passage data in rows 10+ in Columns A-C. Make sure that only data in Row 9 remains. You can either hit the ‘delete’ key, or right-click and select ‘Clear Contents’.

Step 2: Set the values in B9 (Time Zone Adj) and C9 (End Mileage) to 0.

Step 3: Enter the Average Nautical Miles per Day you anticipate being able to sail in Cell F1. For Starry Horizons, we typically plan on 150nm/d and can usually do this pretty easily.

Step 4: Enter the Estimated Total Nautical Miles of the passage in Cell F2. I usually look at the rhumb line distance and add some extra cushion. Figuring out how much extra cushion is a bit of an art form as it varies from passage to passage depending on where the wind will be coming from, the wind angles you’re willing to sail and how much tacking/gybing you will do.

Don’t worry if this number isn’t perfect at the start. It’s not set in stone. You can adjust it while you’re on passage to keep your estimates accurate.

Step 5: Set the Start Trip Log Mileage in Cell F6 to 0. This number is intended to allow you to input whatever number the trip log on GPS is showing at the start of a passage. It makes tracking data during the passage much easier, but it is not needed for planning purposes.

Step 6: Enter the time you intend to depart in Cell B3. Again, I’d suggest clicking on Cell B3 to select it, but then edit the data/time up in the Formula Input Line. That should prevent the date/time format from getting messed up.

Step 7: Make sure the date/time in Cell A9 matches what you’ve entered for your Departure. If needed, make sure to edit the date/time in the Formula Input Line.

The spreadsheet is now ready to play around with and plan your passage. I like to look at the data in Column J to start and get an idea of what time we’d arrive at our destination, based on the information I’ve entered. If this time would have us arriving while it is dark, I’d change the Arrival date/time in Cell I2 to show a desired arrival time before sunset.

You’ll quickly be able to see if the VMG needed (cell I3) is feasible or not. You can also play around with your departure time in Cell B3 (remembering to also change the value in A9!) to see if that would make a before sunset arrival possible.

If it doesn’t look like we’d be able to arrive before sunset, then I’d adjust the Arrival date/time in Cell I2 to be after sunrise the next day. I can then play around again with our Departure Time (Cell B3) so that we can manage to sail the entire time, and not have to hove-to to wait for sunrise.

I should also mention that the longer the passage, the more you should take this as just a general guide. A lot can happen on long passages with various conditions making it hard to judge your average NM sailed per day or your total miles sailed. But for short passages, say an overnight or two, this really helps us figure out departure times to make sure we arrive in daylight!

Tracking A Passage

The real power of the spreadsheet comes in tracking your progress while you’re on passage. Besides just being fun to compare your daily runs (and past passages!), having a continually updating ETA means we can easily make informed decisions whether it’s worth it to speed up (motorsail or use the bigger sails) to ensure we arrive before sunset, or reef several days away from our destination to slow down and arrive without having to heave-to. That saves a lot of guess-work and discomfort!

Here’s how we update the spreadsheet to track a passage:

Departure

Step 1: Update your Departure Time in Cell B3 with the time you actually departed. Also update the Date/Time in Cell A9 with the same time. Again, I’d suggest clicking on the appropriate cell to select it, but then edit the data/time up in the Formula Input Line. That should prevent the date/time format from getting messed up.

Step 2: Set the Starting TL Mileage value in Cell F6 to whatever your GPS trip log is reading at the start of the passage.

End of Day Update + Prepping for a New Day

Step 1: Establish a set time that you will make sure to update the spreadsheet every day. For us that is 7AM. Every day at this time you will update the data in Columns A-C. Let’s start with the day after our departure, the first time we hit a 7AM log.

Update the Date/Time in column A. Again, I’d suggest clicking on the cell to select it and then editing the date in the Formula Input Line.

Step 2: Update the End Mileage in Column C with what your GPS trip log is showing at the time you’ve chosen to update the spreadsheet.

Step 3: If you’re doing long distance East to West or West to East passages, you may cross into a new Time Zone. Or you may be like us and just want to adjust your clocks to keep dinner right before sunset so you’re not cooking in the dark. The Time Zone Adj column allows you to do this.

To account for the extra hour going from East to West (or setting your clock back) add +1 into Column B. This has the effect of making the spreadsheet add 1 hour to the period for which Total Miles (Column E) and Average SOG (Column F) are being calculated.

To account for losing an hour (going from West to East) add -1 into Column B. This has the opposite effect and shortens the time period being calculated.

Step 4: Once Steps 1-3 are done, the day is complete and it’s time to start a new day. Select the cells in Columns A-C, copy the data using Ctrl+c (PC), Cmd+c (Mac) or Right Click and paste it into Column A of the next row using Ctrl+v (PC), Cmd+v (Mac) or Right Click.

Note: It’s unlikely you’re going to have two Time Zone adjustments in a row, so make sure you update Column B for the new day!

The spreadsheet is now ready for a new day. But you don’t have to wait 24 hours until you update it again.

Mid-Day Updates

I strongly suggest that each row end up tracking a 24 hour period of time. That way you’re consistently tracking the miles sailed and average Speed Over Ground (SOG) and have meaningful data.

However, there is nothing in the spreadsheet that requires you to do that. I frequently update the new row of data throughout the day to track our progress. Then when 7AM rolls around again, I update the row for the final time, repeating steps 1-4 of the ‘End of Day + Prepping for a New Day’ section.

Here’s how I track our progress throughout a single day:

Step 1: Update the day/time in Column A at whatever time you want to check your progress. Let’s say it’s now 1PM, still on February 15th.

Step 2: Update the Mileage in Column C with the trip log reading on your GPS.

I will do this quite often throughout the day. It’s especially fun if we’re on track for a 200nm day! (Hint: that’s about a 8.4knot average!)

Then once 7AM rolls around, I follow steps 1-4 of the ‘End of Day + Prepping for a New Day’ section for every day of the passage. By the end of a passage it looks something like this:

Mid-Passage Analysis

Distance Remaining

Once we are several days into a passage, it starts to become apparent whether or not the assumptions we made during our planning phase were appropriate. The biggest one that affects the accuracy of the calculated ETAs is the Estimated Total Miles in Cell F2.

I constantly keep an eye on the miles remaining as calculated by the spreadsheet (Cell F4) and the distance to our destination as tracked by our chartplotter. If the difference becomes significant, I update the spreadsheet to try and increase the accuracy of the calculated ETAs.

Desired Arrival Time

The Needed VMG (Cell I3), Needed Nautical Miles per Day (Cell I4) and Days Left (Cell I5) are constantly being updated as you enter more data through-out the passage. As we get closer to our destination, I’m watching the Needed VMG number to see if we’ll be able to make it at our current speeds. If not, then I change the Desired Arrival Time (Cell I2) to sunrise the next day. This way I can see how fast we can sail and still arrive in daylight.

SOG ETAs

Passages inevitably have a variety of conditions. If you had calm air at the beginning but the winds (finally!) filled in, your overall SOG may be slower than you’d expect to average over the rest of the passage.

In Cell M2, you can choose a SOG that you’re currently averaging, or even one that you think you’ll average for the rest of passage and the spreadsheet will calculate Estimated ETAs off of these numbers.

Arrival Ranges

There is a whole sheet dedicated to calculated ETAs based on various speeds. You can access this sheet at the bottom of the screen. Sometimes I’ve found it helpful to have a quick glance and see what speeds are needed to arrive before sunset or after sunrise.

ETAs are both dynamically calculated with how many miles are remaining on your passage and a basic calculation that uses your assumptions during planning.

Conclusion

We use this spreadsheet all the time for every single one of our passages. It’s been a lot of fun tracking how well we’ve sailed all the way around the world and I hope you find some good use out of it as well!

Enjoy!