Maintenance log

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.

Service items

 

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.

Work done

 

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.

Warning thresholds

 

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.

Acknowledgements

 

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.

Expiration schedule

 

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.

Implementation details

 

The full spreadsheets are at:

 


If your comment doesn't show up right away, send us email and we'll dredge it out of the spam filter.


6 comments on “Maintenance log
  1. Hi James
    Thks for your entry – valuable and highly useful.
    I am presently considering a secondhand 47′ and would be interested in stalling the Matron equipment here. Have received the ‘N2K Builder_UM_1 116’ and wonder if you are willing to post your DIRONA details here as well. It would give me a good comparison and be of great help in evaluating details and cost. Was your installation done from the PAE factory or did you do it yourself??
    Your are welcome to reply by mail directly.
    Thks for your entries -interesting and great for a NordhavnDreamer.
    Best regards
    Erik/Denmark

    • Hi Erik. Sure, I’m happy to post more data on the Maretron system but I don’t have a current n2kbuilder example. What we could post is the n2kanalyzer output showing all the attached devices and the underway and at rest screens. I’ll plan to do a blog posting with that data — let me know if that will cover your interests.

      The NMEA backbone was installed at the yard but all devices where attached since delivery of the boat. I don’t think I gained much in running the backbone NMEA cable at the boat yard. Later this week, I’ll get a blog post up with the data you are after.

      • Erik Andersen says:

        Hello James – great will look forward to this and am convinced this will cover for the time being. Will test the builder meanwhile..

  2. renaldi says:

    Thank for posting

  3. Peter Le Lievre says:

    James,
    Thanks for posting these spreadsheets. They are just what I am looking for as I start the maintenance plan for Sorpresa. This summer we’ll leave SFO and up for Desolation Sound. Old news for you but we’ve never been!
    Kind regards,
    Pete
    PS> Glad you missed the Sydney storms.

Leave a Reply

Your email address will not be published. Required fields are marked *