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

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, “”))))))))))

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.

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”, “”)))))))

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.