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
Comments
-
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!
GenevieveNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That's sorted it!!
Thank you very much for your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!