Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hello,
Based on example from sheet provided, I'm looking for formula to populate in the At Risk column to flag as At Risk if Final Due Date is seven days out and the Status does not equal "Yes" and % Complete is not 100%
Any support is appreciated!
Try this formula:
=IF(AND([Final Due Date]@row <= Today(7), [Final Due Date]@row >=Today(),Status@row <>"Yes", [% complete]@row <>1), 1, 0)
I very much appreciate the help! Just what I was looking for.
You're welcome. Glad I could help out!
I need help using this formula. I used and received the #UNPARSEABLE error msg. I'm creating a blank dashboard template.
Hi @Cee Johnson ,
@Mike Wilday 's formula, with your column titles, should work:
in the column At Risk:
=IF(AND([Due Date]@row <= Today(7), [Due Date]@row >=Today(), Status@row <>"Yes", [% complete]@row <>1), 1, 0)
Your Risk column needs to be a checkbox, Date a date, and the rest Text/Number.
Work?
Mark
Thanks Mike I tried the formula provided but I received error msg. see my screenshot.
Also, having a problem with the Status column with RGYB balls and the IF formula to execute it. I want the logic to be Green=In Progress, Yellow=Not Started Red=Delayed and Blue=Completed. Thanks for your help! I am able to create formulas in Excel but Smartsheets is a beast.
This formula works for me, but the status column needs to be a check box. From you question above it sounds like your status is a RYGB symbol, in which case the Status formula would change to Status@row<>"Blue":
[At Risk] column:
=IF(AND([Due Date]@row <= TODAY(7), [Due Date]@row >= TODAY(), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)
Changing Ball colors is a nested IF statement, as you said. I don't see a column with Delayed, Completed, Not Started, or Completed so I'm assuming that you want the ball color to reflect the status based on % Complete. For the delayed status the formula determines, based on straight line, how much progress should have been complete as of today against that reported as % Complete. You may want to adjust that:
=IF(NETDAYS(Today(),[Planned End Date]@row)/NETDAYS([planned start date]@row, [Planned End Date]@row)< 1-[% complete]@row, "RED", [% Complete]@row = 0, "Yellow", IF([% complete]@row = 1, "Blue", IF([% complete]@row "In Progress")
Help at all?
At Risk is not working and yes I want the balls to change per the %Complete column but the formula provided didn't work.
Where can I find shortcuts to formulas like Excel? This is too complex.
OK, we'll try again. Thanks for being patient. Here are 2 formulas that should work.
AT RISK: =IF(AND([Due Date]@row <= TODAY(7), [Due Date]@row >= TODAY(), Status@row <> "Yes", [% Complete]@row <> 1), true, false)
BALL COLORS: =IF((NETDAYS(TODAY(), [planned end date]@row) / NETDAYS([planned start date]@row, [planned end date]@row)) < (1 - [% Complete]@row), "Red", IF([% Complete]@row = 0, "Yellow", IF([% Complete]@row = 1, "Blue", "Green")))
Hope for success.
Function help can be found at:
=IF([%Complete]@row = 1, "Blue", IF([%Complete]@row > 0, "Green", "Yellow")) IF(Finish@row<[Due Date]@row=TODAY(7), "Red"))
Excel is so much easier.
Hi Mark,
I didn't get it to work. My prior comment is a screenshot of the Status column formula not working. It only works if I am doing YGB. It doesn't work once I add Red or Due Date.
Both formulas used for both columns aren't working. Any suggestions?
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
