Report with What and When requirements

Elisabeth

Hello community,

I need your help on this report. The solution is probably very easy, I just cannot get my head around it.

I have a project plan with one column "Flag" (lines can be flagged with the little red flag) and a column with the finish date. Now I want to create a Report, which shows me all "flagged" tasks, as well as all tasks, which are late in the project plan.

Within the report builder, I have selected the requirements WHAT and WHEN (see screenshot). However, the report I get only shows flagged tasks, when they are late, not those which are on time. 

What do I do wrong?

THANKS a lot for your help.

Elisabeth

 

L_123

You can't create this via report builder. You need to add a column to your project sheet (i recommend a checkbox column to keep it easy) and add the following formula to it.

=if(or(Flag@row = 1, and(or(isblank([Finish Date]@row),[Finish Date]@row < today()),or(Status@row = "In Progress",Status@row = "Not Started"))),1,0) 

Drag this down the checkbox column, then reference the checkbox column in your report. If the checkbox column is checked, then show it in the report. You should only have this one criteria in your report. You can hide the checkbox column after creation.

Elisabeth

This is brilliant! Thanks a lot for your help. 

It worked perfectly well.

Have a lovely day.

E.

Elisabeth

Hello!

I think I have to ask you again for support.

I've used this formula and wanted to modify it, but somehow cannot get my head around it....

The checkbox should be marked, if

• the line has been flagged/ clicked in the "FLAG" row OR
• the line is past the deadline [Finished Date] AND the Status is either "In Progress" or "Not Started"

Could you help me putting this together??

Thank you so much and have a lovely day!

Elisabeth

Paul Newcome

@Elisabeth What is your current formula?

Elisabeth

Hi Paul,

The current formula also checks boxes, which have no Finish Date. I tried to delete the part "isblank([Finish Date]@row)", but that doesn't work.

Paul Newcome

Are you referring to the formula posted above by L@123?

Elisabeth

Yes, this formula is the one I was using.

Paul Newcome

If you only want to flag rows that have a finish date and remove the argument of Finish Date being blank, try this...

=IF(OR(Flag@row = 1, AND(ISDATE([Finish Date]@row), [Finish Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "Not Started"))), 1)

Elisabeth

Thank you so much Paul. It works!

Have a lovely Friday! :)

Paul Newcome

Happy to help! 👍️