Pulling Tasks into a Report Using Multiple IF(AND( formulas in the same cell

JessicaB
JessicaB
edited 12/09/19 in Formulas and Functions

My ultimate goal is to pull data into a report from a project plan.

I want to pull:

  1. Current tasks (Tasks where TODAY falls between the Start Date and the End Date)
  2. Upcoming Tasks (Tasks where the Start Date is within the next 7 days)
  3. Previous Tasks (Tasks where the End Date is within the previous 7 days)
  4. Late Tasks (Tasks where the End Date has passed but the % Complete is not 100% or "Full" using the Progress Bar symbol)

What is the best way to do this?

I was thinking of creating a hidden column with formulas identifying these rows and then using report builder to pull off those columns, but I'm having trouble with multiple IF(AND formulas in the same cell.

These seem to work individually but not together. Will I need 4 different columns?

  1. Current tasks: =IF(AND[Start Date]# <= TODAY(), [% Complete]# <> "Full", "Current")
  2. Upcoming tasks: =IF(AND(([Start Date]# - TODAY() > 0), ([Start Date]# - TODAY() < 7)), "Upcoming Task")
  3. Previous tasks: =IF(AND(TODAY() - [End Date]# > 0,  (TODAY() - [End Date]# < 7)), [% Complete]# = "Full"), "Previous Task")
  4. Late tasks: =IF(AND(TODAY() > [End Date]#, [% Complete]# <> "Full"), "Late")

 

Any ideas or suggestions?

THANK YOU

 

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!