AND / OR / IF : Nested

As the Card View does not give you the option of using dates to view Boards, I wanted to find a way of doing this.

I wanted the following to show in the "Due" Column

Complete
Late
Next 7 Days
Future Tasks

What I didn't want to do, was to have to change the words myself each time as I am running a project with a lot of lines.

I have been trying to solve the issue of multiple nested statements using IF/AND/OR

This is what I was able to achieve using the following

=IF(Status5 = "Complete", "Complete",

IF(AND(Finish5 < TODAY(), (OR(Status5 = "In Progress", Status5 = "Not Started"))), "Late",

IF(AND(Finish5 > TODAY(), Finish5 < (TODAY() + 7), (OR(Status5 = "In Progress", Status5 = "Not Started"))), "This Week",

"Future")))

This is the Grid View

Board View

This is a much nicer view when looking at progress when you don't always need the date (similar to the MS Planner App) because you can then filter this for those that are assigned and take out the items that are completed.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What are the different options in the Status column?

Purnima Gore

Complete, Not Started, In Progress. You can add as many as you need and then add the relevant AND OR combination. On another sheet I have “On hold” and “Cancelled” and I will add the OR statements so that Cancelled tasks are shown as complete and “On hold” are shown as “Late”.

Cierr Central

And i should have added to make the card view work, make sure that the column type is single select (which I think is the key for being able to use the card view)

Paul Newcome

Try this...

=IF(Status@row = "Complete", "Complete", IF(Finish@row < TODAY(), "Late", IF(Finish@row < TODAY(7), "This Week", "Future")))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.