Formular to create due time status based on Monthly, Quarterly & Annually


I’m creating a maintenance schedule but I’m having difficulties create a formula that shows me the “due status” based on how many days ago an item was serviced. Individual item have different frequency of service: Monthly, Quarterly, Annually, and so on.

I have a single select field that dictate the frequency for each type or maintenance.

I was able to create a formula to calculate cost using a base cost * how often we perform it, but I’m having trouble applying it to idea I’m trying to perform.

What I’m trying to do is

  • IF an item was serviced today, and its frequency is MONTHLY, It will be “On Time” for 30 days, by the 25th to 29th day it will “Be Due Soon”, On the 30th day it will be “Due today”, and on the 31st or passing the 31st day will be “Overdue”.

This is what I kind of have but it’s probably wrong.

You’re on the right track. Check out this base, which I think does what you’re looking for.

To view the formulas, duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

I made Day Difference its own field as it felt clearer to me, but that’s easily changeable

Thank you @Adam_C, You did show me the right path to get to where I want.

I think your idea of having a due date is neat, that way I don’t rely on the status alone.

I did consolidated the calculation into one formular to save me and extra field but having the extra field initially definitely help with where I want to set the number.

I also adjusted the due date from month to days just to match it up with my status field.

Thanks very much for the help.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.