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