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

  1. Complete
  2. Late
  3. Next 7 Days
  4. 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",


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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!