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:
- http://mvdirona.com/blog/content/binary/DironaMaintenanceSchedule.xlsx
- http://mvdirona.com/blog/content/binary/DironaExpirationSchedule.xlsx.
Update 11/23/2018: For those who have additional pieces of machinery to track, such as a second generator, we’ve posted our most current version of the spreadsheet that has been updated to include four “other machinery” entries at Updating the Maintenance Log.
i wwould like to try to se it
It’s posted here: http://mvdirona.com/blog/content/binary/DironaMaintenanceSchedule.xlsx
James, Jennifer,
This is great, I just adapted it for our GB42 and it will be super handy to keep on top of the many systems that are hidden our of sight.
Cheers,
Matt Vasey
That’s great. We’re glad it looks like it’ll work for your Grand Banks.
Hi – I just came across this – It looks wonderful – I’m a very detailed oriented person and this looks like it fits that bill. I can’t however see a link to download this Excel Marvel – could you send me one please?
Craig,
The full spreadsheets is at:
http://mvdirona.com/blog/content/binary/DironaMaintenanceSchedule.xlsx
Jennifer
Thank You for sharing
Is there a reason for some of the cells having their own special conditional formatting – cell b22, b105, b100? Here are all of the conditional formatting cells. I am sure I will figure it out, and will maybe just stick with the last two conditional formula. I realize this is not turn-key and you have to understand Excel really well. I really like the fact that it is Excel and not a fancy application that could go away someday!
Main =IF(ISBLANK(J22) = FALSE, 1, 0) b22 – drain water off fuel filter $B$22
Main =IF(OR(IF(ISNUMBER(C22),((NOW()-F22)/30>C22),FALSE), IF(ISNUMBER(D22),((O22-G22)>D22),FALSE)),1,0) $B$22
Main =IF(OR(IF(ISNUMBER(C22),((NOW()-F22)/30>C22-M22),FALSE), IF(ISNUMBER(D22),((O22-G22)>D22-N22),FALSE)),1,0) $B$22
Main =IF(ISBLANK(J105) = FALSE, 1, 0) b105 – lubricate bicycle chain $B$105
Main =IF(OR(IF(ISNUMBER(C105),((NOW()-F105)/30>C105),FALSE), IF(ISNUMBER(D105),((O105-G105)>D105),FALSE)),1,0) $B$105
Main =IF(OR(IF(ISNUMBER(C105),((NOW()-F105)/30>C105-M105),FALSE), IF(ISNUMBER(D105),((O105-G105)>D105-N105),FALSE)),1,0) $B$105
Main =IF(ISBLANK(J100) = FALSE, 1, 0) b100-104 top off $B$100:$B$104
Main =IF(OR(IF(ISNUMBER(C100),((NOW()-F100)/30>C100),FALSE), IF(ISNUMBER(D100),((O100-G100)>D100),FALSE)),1,0) $B$100:$B$104
Main =IF(OR(IF(ISNUMBER(C100),((NOW()-F100)/30>C100-M100),FALSE), IF(ISNUMBER(D100),((O100-G100)>D100-N100),FALSE)),1,0) $B$100:$B$104
Main =R10 $B$57:$B$58,$B$71:$B$79,$B$48:$B$53,$B$83:$B$99,$B$37:$B$44,$B$10:$B$22,$B$26:$B$33,$B$62:$B$67
Main =IF(OR(IF(ISNUMBER(C10),((NOW()-F10)/30>C10+P10),FALSE), IF(ISNUMBER(D10),((O10-G10)>D10+Q10),FALSE)),1,0) $B$57:$B$58,$B$71:$B$79,$B$48:$B$53,$B$83:$B$99,$B$37:$B$44,$B$10:$B$22,$B$26:$B$33,$B$62:$B$67
Main =IF(OR(IF(ISNUMBER(C10),((NOW()-F10)/30>C10-M10+P10),FALSE), IF(ISNUMBER(D10),((O10-G10)>D10-N10+Q10),FALSE)),1,0) $B$57:$B$58,$B$71:$B$79,$B$48:$B$53,$B$83:$B$99,$B$37:$B$44,$B$10:$B$22,$B$26:$B$33,$B$62:$B$67
Laura,
I didn’t check the expiration schedule spreadsheet—I’d forgotten there were two.
I’ll fix both spreadsheets tomorrow so that they’re both absolutely error-free.
On your question about =R10, that is referring to the value in hidden column R. R10 will be 1 if the J10 in the Acknowledge column has a value starting with “ACK:” in it, and that will show the item in green. 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. Entering “ACK:xxx”, where xxx can be any text, 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.
Jennifer
I am not sure if my comment got in or not – regarding the maintenance spreadsheet has one conditional formula that I am not sure what the function is:
=R10 ( on the range: $B$57:$B$58,$B$71:$B$79,$B$48:$B$53,$B$83:$B$99,$B$37:$B$44,$B$10:$B$22,$B$26:$B$33,$B$62:$B$67)
I will try to figure it out, but the sample expiration spreadsheet has REF# in some of the formulas ( which means the formula references cells that no longer exist). I am trying to figure out what the conditional formatting REF# cells should be referring to.
Laura,
Sorry you’re having trouble with the spreadsheet. I don’t see any fields display REF#–is it possible you’ve edited/cleared something? Note that there are some hidden columns M-R that the other formulas reference–you might have accidentally deleted them.
If that isn’t the problem, could you tell me the row/column of one of the cells where you see REF# and the formula in that cell?
Jennifer
First thank-you so much for responding. The spreadsheet is amazing!! I have been able to dive deep into the logic and how the spreadsheet works to have it all work perfectly for me! Now I am moving onto the maintenance schedule!
The REF# references were in the Conditional Formatting for the worksheet. I was able to delete the rules that have “REF#” in them ( the REF means they were were referring to fields that do not exist). This was a problem with the base .xlsx before I made any changes at all.
Thank-you for sharing your work!! We just bought a new-to-us boat. I hope to be really good about keeping track of all maintenance and upgrades. This really paid off well for the motorcycle I just sold, although simpler records :)
The posted spreadsheet has no #REF errors in the Conditional Formatting. It’s easy to change something and create one though. The best way to make changes to the spreadsheet is one at a time, looking for any problems as you go, and undo the change if you get an error.
Glad you got it working, and hope you enjoy your new boat.
Jennifer
You can see the #REF in the conditional formula – here are all of the conditional formulas. Look at line 6. I hope this helps, but I am all set with the spreadsheet with making changes. It is great!!! Thank-you.
Sheet Formula Range
ExpirationDates =IF(ISBLANK(F8) = FALSE, 1, 0) $B$8
ExpirationDates =NOW()>C8 $B$8
ExpirationDates =IF(ISBLANK(F6) = FALSE, 1, 0) $B$14,$B$6:$B$10,$B$18:$B$20
ExpirationDates =(C6-NOW())/30 <= 0 $B$14,$B$6:$B$10,$B$18:$B$20
ExpirationDates =(C6-NOW())/30#REF!),FALSE), IF(ISNUMBER(#REF!),((#REF!-D14)>#REF!),FALSE)),1,0) $B$14,$B$18
ExpirationDates =IF(OR(IF(ISNUMBER(#REF!),((NOW()-C14)/30>#REF!-L14),FALSE), IF(ISNUMBER(#REF!),((#REF!-D14)>#REF!-M14),FALSE)),1,0) $B$14,$B$18
I should have added that the formulas are what came directly with the sheet provided in your link. This is without me making any changes at all. But again, I am all set, and love what I have working with my expiration sheet – all works great!
Hello James,
Nice spreadsheet and thank you for sharing. I just bought a sailboat with a diesel engine.
You say that
“…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…”
I am looking for how updating C1 cell automatically updates H14, but I do not see C1 referenced in the H15 formula. Is C1 value used just for manual tracking?
Also, what is the 4.34 multiplier referencing or correcting for?
Thank you!!
Glad you find the spreadsheet helpful.
The hours at the top (C1 etc) are used for the calculation of “Hours left” in column I (based on the values in hidden column O).
The calculation for “Weeks left” in column H only depends on the last service data and the “Months due” value in column C, so won’t use hours from C1 etc. The 4.34 multiplier is to convert from months to weeks.
Jennifer
Excellent, thank you!
SO nice of you two to share this! I was going to sit down and build something like this from scratch, but you just saved me hours. : )
Good to hear. I hope it works well for you.
James,
My version of your spreadsheet (modified for my vessel) seems to have lost its ability to color code items based on date timing (yellow, red, etc.). I cannot find the location for the conditional formulas that control the text colors. Could you please clarify where I can find the formulas?
Mac,
The color coding is based on the values of hidden columns M-R. See the bottom of this blog post for an explanation with a screenshot.
Jennifer
Hi James
just found your maint log schedule. tried to import it onto my iMac and a number of formula wouldn’t transfer , any suggestions ?
Regards
Paul
It sounds like some incompatibility between the Excel or whatever you are using on your iMac. When you say “import” it sounds like you might not be using Microsoft Excel. If you aren’t, that or Office365 should open the spreadsheet without problem. If you are using Microsoft Excel might try latest patches or installing the new version. If that isn’t practical, Google docs will probably read the spreadsheet: https://www.google.com/sheets/about/.
James, just found this blog and your maintenance spreadsheet. I’ve been looking for a good tool for tracking maintenance on our 34′ PDQ power catamaran. Question… could you please clarify to purpose of the “Base Hours” column?
Hi Mac. Glad to hear the spread sheet might be useful to you. The “base hours” column is explained in the blog: “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.” (https://mvdirona.com/2015/03/maintenance-log/).
We’re just picking up the current engine hours from the top left hand corner of the spread sheet and repeating it as a hidden column. This just makes it easier to code the rest of the spread sheet and can be and should be ignored. If you just copy full rows or delete full rows, everything will work fine. Base hours is essentially just a programming convenience to make the spread sheet simpler to write.
James, thanks for your reply. Another question… what do you do for a maintenance “log”? (a chronological history)
The maintenance log probably should have been done using a database application but the excel version is easy to use and serves to remind me to do what needs to be done. We have a separate boats log where I keep all boats movements, consumption, uses, anomalies, events, and maintenance. The excel spreadsheet reminds to get the work done and the ships log is where the record is kept.
Hi James,
Thanks for providing your maintenance log. We are the new owners of a Kadey Krogen Whaleback and are slowly getting our heads wrapped around all the systems. Your log will be a great help in keeping things maintained properly. Do you have an example of your Ships Log that you’d be willing to share? I did search the blog and couldn’t turn it up.
BTW, I really enjoy your blog. Very inspiring to us as we start to plan our travels.
Our ships log isn’t very automated. We just use a word document with a computer generated trip summary that we just copy into the log. More detail on it here: https://mvdirona.com/2020/08/ships-log/.
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.
Hello James – great will look forward to this and am convinced this will cover for the time being. Will test the builder meanwhile..
Thank for posting
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.
Glad to hear that the maintenance log will work for you. Enjoy Desolation Sound, we have been up there many times and really enjoy it. Our favorites were sunny winter days where the weather was nice, it’s comfortable on deck, the circling mountains are snow capped, and there is not another boat for miles. It’s like a trip back in time to a period when far fewer boats visited.
While you are up that way, you should get the Waggoner Cruising guide (http://www.waggonerguide.com) and, if you like to get a bit off the beaten path, our cruising guide might help as well: http://www.amazon.com/Waggoner-Cruising-Guides-Secret-Coast/dp/0935727299
–James Hamilton
–Location: Great Barrier Reef (//mvdirona.com/maps)