Report with What and When requirements

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

Annotation 2019-11-20 164452.jpg

Find more posts tagged with

Reports

Comments

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! 👍️

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…