Forum Discussion
Equipment Maintenance Spreadsheet
In my example i have formula
=500-MOD(C2,500)
to calculate the remaining hours until the next service and the rule
=IF(500-MOD(C2,500)<20,1)
for conditional formatting.
OliverScheurich Thanks for the response! That's a great idea to use hours until next service. I was able to get that formula to work on mine for the countdown. However what would be even better is if it read hours since last service and counted up. I attached my spreadsheet that I'm working on for reference. I got the "hours since last service" column to count up from the "last service" column, by entering the hours in the "hour reading" column and simply calculating the difference. However I would love for it to turn red when it goes over 500 hours since last service. I had some trouble getting your second formula to work. I attached the error I am given. Again sorry for the Excel ignorance, I just haven't used it much. Thanks again for your help
To apply conditional formatting please follow this path:
Home / Conditional formatting / New rule / Use a formula to determine which cells to format
Now you can enter a formula (rule) for conditional formatting in the field under:
Format values where this formula is true
In order to format the cells when column "hours since last service" is more than 500 enter below formula:
=IF(K4>500,1,0)
and choose your format.
According to the size of your sheet in the manager for conditional formatting in the field "applies to" you have to enter:
=$K$4:$K$110
I attached picture of how to enter data for conditional formatting, however it is from the german version of excel.
