Hi all, need help with a formula to flag the at risk column.

Richard2014

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"

Richard2014

Formula works perfect, thank you sir!!

Paul Newcome

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))

Richard2014

LOL, thank you Paul, guess I need to stop complicating these formulas. Yours works perfect, very much appreciated sir!

Paul Newcome

Try something like this:

=IF(IF([QC Date Completed]@row <> "", [QC Date Completed]@row, TODAY()) > [Internal QC]@row, 1)

Richard2014

Formula works perfect, thank you sir!!

Paul Newcome

Happy to help. 👍️

Richard2014

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.

Genevieve P.

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?

Richard2014

Thanks for the response, was trying to get this formula to also apply to other columns, I have circled them below.

Genevieve P.

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?

Richard2014

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.

Paul Newcome

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))

Richard2014

Running it through a few tests but so far so good, thank you Paul, very much appreciated!

Paul Newcome

Happy to help. 👍️

Genevieve P.

Thanks, Paul! 😎

Paul Newcome

@Genevieve P. Teamwork makes the dream work. Haha.

Richard2014

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")))

Paul Newcome

@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)))