On our previous boat, we were (just barely) able to remember when all mechanical systems needed service as the hours mounted and time passed. But that system broke down when we got the current boat. There are far more systems, many of the intervals vary greatly, and when running offshore, up-to-date service is even more important. We knew we needed some form of automation to help track all the systems and their varying maintenance schedules.
Many approaches to maintenance logs exist and, in fact, entire businesses have been built on tracking service items and notifying when service is needed. We are fairly hands-on and, for long-range cruisers, even though it takes time to understand all the systems, this feels like time well spent. One thousand miles from shore, there won’t be someone else flying in to help. A good approach for maintenance and service tracking is to use a database, and drive all tracking and notification from a software application built upon the database. We still may take this route someday in the future, but didn’t feel like taking the time to write this complete application up front. Instead we elected to use an Excel-based spread sheet model. Just as many exotic financial instruments are first implemented in spreadsheets and only migrate to full applications when they are successful and need to scale, we went first to Excel. It’s actually working out remarkably well.
The log contains one entry per service item, where each item has an associated number of hours and/or number of months between service intervals. For example, in the screenshot below, the cells highlighted in blue indicate the main engine oil and filter are due to be changed every 6 months or 375 hours. This information is entered per service item, one time only. Service intervals can be expressed in only hours or only months. In the screen shot below, the start batteries need to be tested every 12 months, but have no hour-based service interval, and the harmonic balancer should be changed every 8,000 hours, but has time-based service interval.
The top of the sheet lists the current number of hours for each piece of equipment. For example, the number of main engine hours, 5,208, are highlighted in yellow below. When we update the number of hours on a piece of equipment, the log tells us when the next service is due in 1) hours for each item that depends on that piece of equipment and/or 2) months based on the current date. In this case, the cells highlighted in green indicate that the main engine oil and filter are due to be changed in 4 months or 93 hours.
As service work is performed, we enter the date and/or number of hours on the associated equipment when the work was done. In the screen shot below, the main engine oil and filter were last changed on 2015/01/09 at 4,926 engine hours. This data, combined with the current date and the main engine hours at the top of the spreadsheet, are used to calculate the months and/or hours left before the next service is required.
The most useful aspect of the spreadsheet is the highlighting of a service item near due or overdue. In the screenshot below, we’ve incremented the number of main engine hours by 200 to 5,408. The main engine oil and filter change is now overdue by 107 hours, and that service item now shows in red. In addition, the service item to clean the air filter is orange. This indicates that the service item is near due, but is not yet overdue. The orange coloring uses the warning thresholds that we’ve highlighted in blue below. In this case, we want to display a service item in orange if it is within 1 month or 50 hours of being due.
Service items are grouped based on the equipment they depend on, if any, and the warning threshold. For the wing engine below, we have a warning threshold of 1 month and 15 hours. We could also choose, for example, to add another main engine group with a different warning threshold, such as 25 hours.
When a service item is near due or overdue, the normal way to change the text color back to black is to perform the service and update the date and/or associated equipment hours when the work was done. But sometimes this either isn’t feasible or necessary. That is where the Acknowledge column comes in. The screenshot below shows that the stabilizer fin zincs are 37 hours overdue for a change, but that’s best done in the yard. So we’ve entered “ACK:Yard” there and that changes the color from red to green. This still highlights the item as not done, but showing it in green will keep it visually separate from items that we really do want to handle as they become due. The “ACK:” portion of the text is all that is necessary for the acknowledgement, anything after that is for descriptive purposes only.
Extending a due date is different from an acknowledgement. The bottom cleaning currently is due after 3 months, but on inspection it’s still pretty good, so we’ve entered “M:1” below to extend the due interval by 1 month. In Seattle we used to change our zincs every 6 months. But since we’re not in marinas as frequently, we’re able to go longer as we’re no longer supporting other boats in the marina. So we instead check the zincs periodically and extend the due interval if they are in good shape. Below we’ve extended the due interval for the zinc change by 12 months using “M:12”, and it shows as 11 months overdue. The 12-month extension makes the zinc change due in April of 2015, 1 month from now. Since that is within the 1-month warning threshold for that service item group, the zinc change shows in orange. We’ll check the zincs soon and either change them or extend the due interval again.
In a similar vein, we also created an expiration schedule spreadsheet for non-maintenance items that we need to track, such as renewing our passports or drivers licenses. These are grouped based on the amount of lead-time needed to process each item.
The text color changes are implemented using conditional formatting. We have added extra columns, normally hidden, that extract some of the data from the service items plus their warning threshold and base hours. This puts the relevant data for a service item on a single row and simplifies the conditional formatting formulas.
The full spreadsheets are at: