Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Topic Labels: Formulas
Solved
Jump to Solution
170 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello,

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.
image

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.
image

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.
image

1 Solution

Accepted Solutions

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

See Solution in Thread

2 Replies 2

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_TheTimeSavingCo, 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.

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

Thanks very much for the help.