Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Is there a way to have a formula tell you if a dea...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Dates & Timezones

Solved

Jump to Solution

0
4741
10

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 09:00 AM

Hi all,

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.

Thanks,

Lauren

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 11:34 AM

Try this:

`IF({Deadline Due Date} < NOW(), "Late", IF(IS_SAME({Deadline Due Date}, TODAY(), 'day'), "At Risk", "On Time"))`

10 Replies 10

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 10:15 AM

Try a formula like this one:

`IF({Deadline Due Date} > TODAY(), "On Time", IF(Birthday < TODAY(), "Late", "At Risk"))`

Let me know if this works for you. If it did, please mark it as the solution.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 10:42 AM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 11:34 AM

Try this:

`IF({Deadline Due Date} < NOW(), "Late", IF(IS_SAME({Deadline Due Date}, TODAY(), 'day'), "At Risk", "On Time"))`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 11:43 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 01:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 01, 2020 01:43 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 02, 2020 12:34 PM

Yes, it is really easy to get lost in nested statements.

I find it easier if I use an external editor and write the formula with multiple lines:

```
IF(
condition-1,
first-true-result,
IF(
condition-2,
second-true-result,
IF(
condition-3,
third-true-result,
final-else-result
)
)
)
```

This formatting makes it easier to spot errors:

- each
`IF`

statement and its closing `) are vertically aligned. - each
`IF`

statement has exactly three lines at the next level of indent that match the three parameters to the`IF`

statement. - each line that represents a parameter ends with a
`,`

unless the following line is a`)`

For very complex formulas, I use a code editor with automatic bracket matching.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 02, 2020 12:58 PM

I sometimes find the vertical formulas hard to follow but it depends on the formula and how complex it is.

If you don’t mind me asking, what external editor do you use to figure out these formulas?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 02, 2020 01:28 PM

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.