IF AND Function For Reporting

Marcus

Hi guys,

I am trying setting up a new report for my team which will allow them to bring through project activities based on the following criteria:

• The week the activity happened in is before today's current week.
• The RYG status is red OR green OR yellow

I am using a column set up as a checkbox and trying to put an IF( formula behind that to trigger a tick if the above criteria are present. 

=IF(AND(WEEKNUMBER(Finish183) < WEEKNUMBER(TODAY())), 1)

This formula seems to work to tell me if the activity happened before the week I am in, however I am having difficulty adding the second criteria on to this.

 

Does anyone know how the formula should be formatted to ask the RYG status as a second criteria, but also only if it is red or green or yellow (don't want to report on blue)

 

Appreciate any help 

 

Kind regards

 

 

Genevieve P.

Hi Marcus,

If you're creating a report, you can actually have RYG criteria built into the Report Builder, meaning that you don't have to create a formula in the source sheet.  



To do so, create a new report and select your source sheet. Go to the "What?" section and choose the column that contains your RYGB status balls. Then in the next window, select the checkbox that indicates a colour should be excluded and choose to exclude Blue.

In my screen capture below, "Health" is the name of the RYGB column in my source sheet and I have decided to exclude any rows with the Gray ball from the report. 

Cheers!

Genevieve

Marcus

Hi Genevieve,

Thanks for responding. That would normally work but I may have missed a piece of information out from my original post. I have a separate column for checkboxing any finish date that is the same weeknumber as today's weeknumber. 

The two formulas I'm using in the report are:

=IF(WEEKNUMBER(Finish24) = WEEKNUMBER(TODAY()), 1)

=IF(WEEKNUMBER(Finish24) < WEEKNUMBER(TODAY()), 1)

I only want the RYG to be limited on the second formula. If I try to add RYG as a 'what' property in the report it won't work as I have the report set up as an or options as per attached screenshot.

I want the report to be able to bring through any activity which happened in this week (regardless of RYG) but also any activity which happened in any week prior to this week (but I don't want to show blue a.k.a complete tasks).

 

Does this make a bit more sense?

 

Cheers

 

Genevieve P.

Hi Marcus,

Thank you for the clarification! Would adding an "AND" function to your formula help?

 

=IF(AND(WEEKNUMBER(Finish24) < WEEKNUMBER(TODAY()), [RAG Status]@row<> "Blue"), 1)

Marcus

That's sorted it!!

 

Thank you very much for your help