Conditional formula to show status based on date

[Otto_Kiefer]

Hi everyone,

quite new to Airtable and being very impressed so far, I’m currently underway managing more complex formulas. Still, what I’d like to accomplish for my task management table is too overwhelming so I ask for a little help :).

I have “Do” dates as well as “Deadline” dates; every task has a “Do” date but not necessarily a “Deadline” date. “Deadline” Dates are only relevant to me if they’re overdue.

.

Here’s what I’d like to get in the “Status” column to be able to group accordingly:

• If Deadline is before yesterday, show “Overdue X days”
• If Do is before yesterday AND Deadline is blank, show “X days ago”
• If Do OR Deadline is yesterday, show “Yesterday”
• If Do OR Deadline is today, show “Today”
• If Do is tomorrow, show “Tomorrow”
• If Do is further in the future, show “in X days”

Seems complex but I’m sure it is possible, so I’d appreciated any help greatly.

Thanks in advance,
Otto

[Otto_Kiefer]

Here’s an update on my behalf.

I’ve started creating the following formula, which works.

IF(DATETIME_DIFF(TODAY(), Deadline, ‘days’) >1, “Overdue " & DATETIME_DIFF(TODAY(), Deadline, ‘days’) & " days”).

By only adding a second condition (which doesn’t has the OR-statement yet, due to my current lack of knowledge), which is quite similar and whose syntax should be correct, I already get an error:

IF(DATETIME_DIFF(TODAY(), Deadline, ‘days’) >1, “Overdue " & DATETIME_DIFF(TODAY(), Deadline, ‘days’) & " days”),
IF(DATETIME_DIFF(TODAY(), Deadline, ‘days’) =1, "Yesterday“)

Another little problem just occured to me:

Even if all the conditions would work, do I understand correctly that I wouldn’t be able to group my tasks by status chronologically using this formula? That means the tasks having an “Overdue” status will not be shown first just because the group sorting is plain alphabetically and so the grouping would be

1. “X days ago”
2. “in X days”
3. “Overdue X days”?
4. “Today”
5. "Tomorrow
6. “Yesterday”?

Oh gosh, I’m confused.

[Justin_Barrett]

Welcome to the community, @Otto_Kiefer! :grinning_face_with_big_eyes: Sorry that this has gone so long without an answer. Let’s tackle those issues one at a time.

Otto_Kiefer:

By only adding a second condition (which doesn’t has the OR-statement yet, due to my current lack of knowledge), which is quite similar and whose syntax should be correct, I already get an error:

IF(DATETIME_DIFF(TODAY(), Deadline, ‘days’) >1, “Overdue " & DATETIME_DIFF(TODAY(), Deadline, ‘days’) & " days”),
IF(DATETIME_DIFF(TODAY(), Deadline, ‘days’) =1, "Yesterday“)

The reason that this generates an error is because you stacked the two IF() functions end to end. What you need to do is nest the second inside the first. The basic layout would look like this:

IF({Difference} > 1, "Overdue", IF({Difference} = 1, "Yesterday"))

Notice how the first IF() function isn’t fully closed until the very end.

An IF() function has three parts (two are required, one is optional):

1. The condition to test.
2. The result to return if the test is true.
3. The optional result to return if the test is false.

When nesting IF() functions, each IF() condition to test (minus the first) should be nested in the optional result-if-false section from the previous one. The more IF() functions you nest, the more closing parentheses you’ll have at the very end of the formula. :slightly_smiling_face:

Regarding your other question about ordering the results chronologically, here’s a how to address it.

First make a formula field that only calculates the difference (I’ll label this {Date Diff} for now):

DATETIME_DIFF(TODAY(), Deadline, 'days')

You can sort by this {Date Diff} field (which can remain hidden), and all the status values will be in chronological order.

Also, instead of repeating that difference calculation over and over to determine the status, you can drop a reference to {Date Diff} anywhere you need that number, which will make your {Status} formula much shorter and easier to maintain.

[Otto_Kiefer]

Hello Justin and thanks so much for your detailed reply and help!

In fact, in the meantime I’ve got the knack of using nested IF-functions myself. At the time of my first posting I’d still been overwhelmed by all the parentheses :).

As for the “sorting” problem, I see and understand your approach.
However, meanwhile I’ve started to define “status” differently to outline the things I’d like to see in a better way.
Accordingly, a task gets a status like “Active”, “Ongoing”, “Overdue”, “Plan” or “Waiting”. These I calculate based on do dates and/or specific checkboxes.
Now, if I’d like to group by these, I don’t have any influence on how they are “sorted” in my table view.

To clarify and if you have the time and interest, you can have a look at an example base I have uploaded to Universe: Comprehensive Task Management.
Thus, to have status “Overdue” shown at the top, I had to use a workaround by putting suitable color symbols in front of the status names whereat the “names” of the color symbols are used to define the sorting.

[Justin_Barrett]

Yeah, that’s just how grouping works. To force the groups to be in a certain order, you have to modify the field you’re grouping by to create that order. You can’t tell it to group by one field, but control the group order using a different field. It would be nice if, in addition to A->Z and Z->A, there was a third option to pick a field. That would allow you to group by the status names, but sort the groups by the date differences, or perhaps some numeric equivalent to the status values. Anyway, that’s an item for the wishlist. :slightly_smiling_face: