A UDF for knowing when to perform a maintenance

[MrByte]

I want to write a UDF (maybe multiple ones) perform calculations for a fleet of vehicles as to when to perform a maintenance or quality check. Not so simple when I factor in some time frames (rated by mileage) these maintenance's need to performed.

So my first scheduled maintenance is to be performed at or before 40,000 miles. My second scheduled maintenance is to be performed on or before the next 20,000 miles or an odometer reading of 60,000. A third maintenance is to be scheduled withing the next 10,000 miles but there is a deciding factor that needs to be considered before the third and subsequent maintenance's. This fork in the road if you will is kind of a "do I need to perform it now" or "can it wait." Meaning at 67,500 miles I need to check when the last maintenance was done and figure out if it over or under the 10,000.

First Maintenance = 40,000
Second Maintenance or more (or initial interval) = 20,000
Fork In the Road (repeat cut-in) = 67,500
Third Maintenance or more (or repeat interval) = 10,000


CASE-1
I perform my first maintenance at 37,500 miles. I am below the recommended 40,000. My second maintenance is performed at 56,250 (56,250 - 37,500 = 18,750 this is below the 20,000). I am doing good at keeping up with the maintenance's. A third maintenance should be performed at 76,250 so long as I am not over the "Fork in the Road" but wait I AM!! So I have to keep an eye on the vehicle till the odometer reaches 67,500. Okay the vehicle reaches 67,500 now I switch to the repeat interval for calculation. So 67,500 minus 56,250 equals 11,250. OH NO! I should have performed the maintenance 1,250 miles ago. I should not have gone over the 10,000. Now I stop the vehicle and get it in for maintenance ASAP. Now I can continue with subsequent maintenance's at every 10,000 miles.

CASE-2
I perform my first maintenance at 40,000. This is optimal or an ideal scenario. Next I perform another one at 60,000. Then the vehicle rolls in at 67,500 and I am now at the fork in the road. I check my calculations 67,500 - 60,000 = 7,500. I am good to go I really do not need to schedule the maintenance. But I do need to make sure the vehicle is back in here by 70,000. Which is 60,000 plus the repeat interval of 10,000.

This is the start of it. Maybe I am going in the wrong direction with this. But some ideas could help.

Function FIRSTDUE(first_maint, current, threshold)

    'FIRSTDUE becomes threshold if no maintenance has been performed
    If first_maint = 0 Then
        FIRSTDUE = threshold
            Else
        
        'FIRSTDUE is performed immediatley because miles have gone beyond the threshold
        If first_maint >= threshold Then
            FIRSTDUE = "Due Now"
                Else
            
            'FIRSTDUE is the first maintenance if the current miles are greater than
            'the current number of flights otherwise FIRSTDUE is the threshold
            If first_maint <= current Then
                FIRSTDUE = first_maint
                    Else
            FIRSTDUE = threshold
            
            End If
        End If
    End If

End Function