Hi all, need help with a formula to flag the at risk column.
Hi all, looking for help with a formula the will flag the cell in the At Risk column if the "QC Date Completed" is greater then the "Internal QC" or if "QC Date Completed" is blank and today is past the "Internal QC"
Best Answers
-
Formula works perfect, thank you sir!!
-
I would suggest something along these lines...
=IF([Installation Date]@row <> "", IF(OR(IF([QC Date Completed]@row <> "", [QC Date Completed]@row, TODAY()) > [Internal QC]@row, IF([EXT QC Date Completed]@row <> "", [EXT QC Date Completed]@row, TODAY()) > [External QC]@row, IF([IFC Date Completed]@row <> "", [IFC Date Completed]@row, TODAY()) > IFC@row), 1))
-
LOL, thank you Paul, guess I need to stop complicating these formulas. Yours works perfect, very much appreciated sir!
Answers
-
Try something like this:
=IF(IF([QC Date Completed]@row <> "", [QC Date Completed]@row, TODAY()) > [Internal QC]@row, 1)
-
Formula works perfect, thank you sir!!
-
Happy to help. 👍️
-
Hi, was wondering if you could add something to the formula, if the "Installation Date" is blank then at risk is not triggered and have the formula apply to 2 additional columns, "EXT QC Date Completed" and "IFC Date Completed"? Your help is much appreciated.
-
Hi @Richard2014
You can add an IF statement at the beginning to check the Installation Date cell, like so:
=IF([Installation Date]@row = "", 0
So:
=IF([Installation Date]@row = "", 0, IF(IF([QC Date Completed]@row <> "", [QC Date Completed]@row, TODAY()) > [Internal QC]@row, 1))
However I'm not sure what you mean by the other column references. Can you show what you'd like it to do with example data in your sheet?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks for the response, was trying to get this formula to also apply to other columns, I have circled them below.
-
Hi @Richard2014
When you say "apply to other columns" do you mean you want to put the formula IN those columns, or you want the formula to look at those columns for empty cells?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Yes, the formula should look at those cells but also should look to see if their date completed has past the previous columns date. Hope that makes sense.
-
I would suggest something along these lines...
=IF([Installation Date]@row <> "", IF(OR(IF([QC Date Completed]@row <> "", [QC Date Completed]@row, TODAY()) > [Internal QC]@row, IF([EXT QC Date Completed]@row <> "", [EXT QC Date Completed]@row, TODAY()) > [External QC]@row, IF([IFC Date Completed]@row <> "", [IFC Date Completed]@row, TODAY()) > IFC@row), 1))
-
Running it through a few tests but so far so good, thank you Paul, very much appreciated!
-
Happy to help. 👍️
-
Thanks, Paul! 😎
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Teamwork makes the dream work. Haha.
-
Hope its ok to keep this thread open, you all have been great.
Trying to get the "Percent complete" column to show 90 when a date is entered into the "QC date completed" column, 95 when a date is entered into "Qc date completed" and "Ext date completed", 100 when a date is entered into "Qc date completed" and "Ext date completed" and "IFC date completed".
Here is the formula I'm trying to use.
=IF([QC DATE COMPLETED]@row <> "", "90", IF(and[QC DATE COMPLETED]@row <> "", [EXT QC DATE COMPLETED]@row <> "", "95", IF(AND([QC DATE COMPLETED]@row, [EXT QC DATE COMPLETED]@row <> "", [IFC DATE COMPLETED]@row <> "", "100")))
-
@Richard2014 Try this one:
=IF([IFC DATE COMPLETED]@row <> "", 1, IF([EXT QC DATE COMPLETED]@row <> "", 0.95, IF([QC DATE COMPLETED]@row <> "", 0.9)))
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
- 145 Just for fun
- 62 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!