Troubleshooting nested IF formula

[Rebecca_Chapman]

Hi - I am using Airtable as a project management tool, each task has start and finish dates (though sometimes the start date is blank) and there are 2 checkbox columns - ‘Started’ and ‘Done’.

I’ve now created another column called ‘Status’ which I want to automatically update using a formula.

However I’m getting stuck with an error whenever the start date is blank and the finish date is before today (in which case the Status should be “Overdue!”).

The formula (edited to remove subsequent parts which are irrelevant to this error) is:

IF({Done?}=1, “Complete”, IF(AND({Start date}="",{Finish date}=""), “Timing TBC”, IF(AND(OR(AND({Start date}="", IS_BEFORE({Finish date}, DATEADD(TODAY(), 7, ‘days’))), IS_BEFORE({Start date}, DATEADD(TODAY(), 7, ‘days’))), {Done?}=0, {Started?}=0), “Coming up”, IF(AND(IS_BEFORE({Finish date}, TODAY()), {Done?}=0), “Overdue!”, “”))))

Essentially, the 1st condition is that the ‘Done’ column is checked -> “Done”
The 2nd condition is that the start & finish dates are blank -> “Timing TBC”
The 3rd condition is that (either the start date is blank & the finish date is within a week of today, or the start date is within a week of today) and it’s not ‘Done’ or ‘Started’ -> “Coming up”
The 4th condition is that the finish date is before today and it’s not ‘Done’ -> “Overdue!”

I think I read somewhere that as soon as a formula returns an error (eg. because of a blank date field) it won’t go any further.

From what I can work out, the error is coming from the 3rd condition, because when I tested a formula with only the first 2 conditions, it returned “” rather than an error.

Can anyone tell me where I’m going wrong?

Thanks!

[Rebecca_Chapman]

PS I’m thinking I could avoid the error by dealing with each of the three scenarios where the start date is blank (ie. finish before today = overdue, finish today or in the next week = coming up, finish more than 1 week away = blank)… but it’ll make the nested IF soooo long and complicated - there has to be a simpler version???

[Rebecca_Chapman]

Ok I have made it work but it involves 10 nested IFs!!!

IF({Done?}=1, “Complete”, IF(AND({Start date}="",{Finish date}=""), “Timing TBC”, IF(AND({Start date}="", IS_BEFORE({Finish date}, NOW()), {Started?}=0), “Overdue!”, IF(AND({Start date}="", IS_AFTER({Finish date}, NOW()), IS_BEFORE({Finish date}, DATEADD(NOW(), 7, ‘days’)), {Started?}=0), “Coming up”, IF(AND({Start date}="", {Started?}=1), “Underway”, IF({Start date}="", “”, IF(IS_BEFORE({Finish date}, NOW()),“Overdue!”, IF(AND(IS_AFTER({Start date}, NOW()), IS_BEFORE({Start date}, DATEADD(NOW(), 7, ‘days’)), {Started?}=0), “Coming up”, IF(AND(IS_BEFORE({Start date}, NOW()), {Started?}=0), “Not started!”, IF({Started?}=1, “”))))))))))

[Justin_Barrett]

Welcome to the community, Rebecca! :grinning_face_with_big_eyes: There are some definitely things in here that could be optimized.

Of the ten levels in your latest version, two return “Overdue!”. The common thread is a check to see if you’ve passed the {Finish Date}. As far as I can tell, the other checks tied to them (via AND()) really aren’t relevant—if the finish date has passed, it doesn’t matter what the other items are; it’s overdue!—so a single check for that is really all you need, and it probably should happen early in the formula. Also, if that check is false, there’s no need for the later comparison between NOW() and {Finish Date} because it’s already been done.

On a related note, you’re making some other checks multiple times as part of larger checks, like looking to see if {Start Date} is empty in multiple places. With something like that, where the presence or absence of one item potentially affects several others, make the check once. If it’s there, you make your other related checks that all depend on it being there. If not, make whatever other checks depend on it not being there. But you don’t make that same check again because you’ve already done it once. Another example of this redundant checking is in your first version, where you looked to see if the {Done} checkbox was unchecked in multiple places, but you’d already looked to see if it was checked at the very beginning of the formula. The only way to get to any of the later parts of the formula is to fail that test, so checking for the opposite state later is unnecessary.

I’m working my way through a revision of your latest version with some of these items addressed, but one part of your original description feels like it’s complicating the process: the fact that the {Start Date} field might be empty. May I ask why that is? I can’t picture a scenario where a project would have a finish date, but no start date.

I almost feel like a revision of your first formula might be easier, but even there the odd (in my view) treatment of the start date feels like it might be bulking out the formula unnecessarily.

[Rebecca_Chapman]

Hi Justin, thank you so much for your detailed & thoughtful reply! I knew the formula was overcomplicated, but my brain was too scrambled to figure out how to simplify.

You’re right that sometimes having no start date complicates it. The trouble is, I’ve based this on a spreadsheet which my team has been using for some time, so they’re all used to a particular way of doing things - which includes only putting a finish date if it’s a deadline, key date or meeting (maybe I could change this… if necessary).

Thank you for the pickup about ‘overdue’ tasks, you’re absolutely right, and my previous formula resulted in overdue tasks showing as ‘Started’ (now changed to ‘underway’) as long as that column was checked.

Thanks to your help I’ve now simplified quite a bit… though it’s still long:

IF({Done?}=1, “Complete”, IF(AND({Start date}="",{Finish date}=""), “Timing TBC”, IF(IS_BEFORE({Finish date}, NOW()), “OVERDUE”, IF({Start date}="", IF(AND(IS_AFTER({Finish date}, NOW()), IS_BEFORE({Finish date}, DATEADD(NOW(), 14, ‘days’)), {Underway?}=0), “Coming up (2 wks)”, IF({Underway?}=1, “Underway”, “”)), IF(AND(IS_AFTER({Start date}, NOW()), IS_BEFORE({Start date}, DATEADD(NOW(), 14, ‘days’)), {Underway?}=0), “Coming up (2 wks)”, IF(AND(IS_BEFORE({Start date}, NOW()), {Underway?}=0), “NOT STARTED”, IF({Underway?}=1, “Underway”, “”)))))))

[Justin_Barrett]

Hmmm…there’s probably still room for optimization, though part of me would rather start from scratch rather than try to dissect what’s there (which I attempted last night without much success).

Would you be willing to detail the various states, and the criteria that must be met for each one? Your first post has a partial list, but things were added in subsequent updates that aren’t on that initial list. I’ll take a crack at it and see what I can do.