Excel issue - maint. tracker

[Aspen_Robert]

Greetings, all. 

I've been tasked with creating a live service interval tracking formula for our preventative maintenance. This maintenance is tracked by mileage on the vehicle. We would like to know when the vehicle has been properly serviced within its parameters.

For example -
Current Mileage - 230,000
Serviced Mileage - 234,000

This would be serviced within good standing because it is within the 4,500-5,500-mile buffer (from last service). 

Currently, I have created this formula -
=IF((C8-C7>4500-5500),"GOOD","BAD")

I know there is a better way to develop with conditional formulas. Can you walk me through this? I would greatly appreciate any help!

[#2]

@Aspen_Robert 

That should be

=IF(AND(C8-C7>=4500,C8-C7<=5500),"GOOD","BAD")

[#3]

Thank you, Hans!

Is there a way to conditionally format to make the cell change to green or red?

[#4]

Another question - Is there a way to create the spreadsheet to have the built in formulas where the service is sporadic like this? 

Or will the formula need to be updated after each service? 

[#5]

@Aspen_Robert 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#6]

Yes. It's do-able. Would just need to know your logic for how many blank rows should go between entries. Is it a fixed amount or does it changed based on a criteria?

[#7]

@HansVogelaar Hi, I have attached a link to the google doc below that encompasses similar data that we will find with the fleet maintenance. I am sure there is a way to do it, but with the data so sporadic across dates, I wasn't sure the best way to have it easily manageable for staff. 

Additionally, the spreadsheet can have a "different look", as long as it is operable for the data. 

https://docs.google.com/spreadsheets/d/1zBv3sKpdfBgxWvrZDMSX07Tc5eYiNDxAlVnfn54O7yE/edit?usp=sharing

Thank you so much for your help! 

[#8]

@Patrick2788 Hi, it changes based on the vehicle service for that month. So, some many go only days with some go months between mileage maintenance. 

Here is an example of what we see - 

https://docs.google.com/spreadsheets/d/1zBv3sKpdfBgxWvrZDMSX07Tc5eYiNDxAlVnfn54O7yE/edit?usp=sharing

[#9]

@Aspen_Robert 

See the attached version. I hope it does what you want.