I am trying to format a field to show whether a deadline is “On Time” (before deadline due date), “At Risk” (same day as deadline due date) and “Late” (past deadline due date/time).
Is this possible? I keep getting lost in my nested if statements and when I add another component, I am getting blank records.
I was able to make this work to show “On Time” and “Late”, but the “At Risk” is what I am still looking for and having trouble with. I think it might have to do with the fact that I am using a Date Field with a time as the subject for the “IF” and I only want the time to come in to factor with Late. For instance, if the due DATE is before today, then it would show “On Time”, if the due DATE is today then it would show “At Risk” and if the due DATE/TIME is after now then it would show “LATE”.
This seems to have worked! Thank you so much! I am going to make this as a solution, but I have a question -
When dealing with dates and < or > - is the further down the calendar we go make the date “greater”? I think that is where I was getting tied up because I am not familiar with how the dates greater than and less than work.
Yes. Think of it this way: the further a date is from the beginning of time, the “greater” a date is. More time has passed, therefore the value of that date is greater than the days that came before.
Alternatively, you could have also used IS_BEFORE() or IS_AFTER() instead of < or >.
Vertical formulas can take a bit getting used to. Simple formulas can be easier to understand if they are written horizontally. However, the more complex a formula is, the more I find that writing them vertically is necessary for me to keep track of all the pieces.
Plus, there are multiple ways of writing things vertically. For example, some people write an if statement with the condition on the same line as the if( part.
Just use whatever method works for you. I only suggested this method because you mentioned getting lost in nested if statements.
I write simple (one line) formulas directly in the Airtable editor. I write short, multi-line formulas in whatever text editor I have handy (often the very basic Notepad that comes with Windows because it opens quickly). I write long, complex formulas in the free code editor Atom, but any code editor would work.