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

JessicaB

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

 

Shaine Greenwood

Hi Jessica,

It sounds like you can add all of the criteria that you need to directly in the report builder window without having to create a formula.

In your report builder's what criteria, you can specify % complete is not "full"

In the when criteria, you can select your within 7 days, etc. criteria. 

Attached is a screenshot of my report builder window with a similar layout.

More on creating reports can be found in the help center: https://help.smartsheet.com/articles/522214-creating-reports