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

Options

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"

Options

Formula works perfect, thank you sir!!

• ✭✭✭✭✭✭
Options

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

Options

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

«1

• ✭✭✭✭✭✭
Options

Try something like this:

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

Options

Formula works perfect, thank you sir!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

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.

• Employee
Options

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?

October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

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?

October 8 - 10, Seattle, WA | Register now

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Employee
Options

Thanks, Paul! 😎

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!