IF AND Function For Reporting

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

Find more posts tagged with

Construction

Finance

Formulas

Reports

Comments

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

Screen Shot 2019-11-18 at 1.12.58 PM.png

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

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…