IF statement help

Options

I have a sheet with multiple sets of dates.

I'm trying to automate symbols (green, red, and yellow) based on project status.

I'm comparing the estimated site survey to the actual site survey.

I want to create an IF statement that automates the symbol.

So what would my statement be if I want:

My estimated date = my actual date, green

My estimated date is greater than my actual date, green

My estimated date is less than my actual date, red

I don't have a date (so it's in progress) yellow

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    This is the basic idea:

    =IF([Actual Date]@row = "", "Yellow", IF([Estimated Date]@row >= [Actual Date]@row, "Green", IF([Estimated Date]@row < [Actual Date]@row, "Red")))

    You will have to replace column names to match your sheet. This also assumes that both columns are correctly formatted as dates.

  • horrlind
    Options

    Hi! Thanks for the response. This formula says unpareseable


    =IF([Actual Mechanical Load Calcs]@row "", "Yellow", IF([Estimated Mechanical Load Calcs]@row >= [Actual Mechanical Load Calcs]@row, "Green", IF([Estimated Mechanical Load Calcs]@row < [Actual Mechanical Load Calcs]@row, "Red")))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    =IF([Actual Mechanical Load Calcs]@row = "", "Yellow", IF([Estimated Mechanical Load Calcs]@row >= [Actual Mechanical Load Calcs]@row, "Green", IF([Estimated Mechanical Load Calcs]@row < [Actual Mechanical Load Calcs]@row, "Red")))

  • horrlind
    Options

    Hi!

    I am changing my request a little bit. I am trying to automate the red, yellow, green buttons still with IF statements. I have 3 columns: An estimated date column, an actual date column, and my column with the status buttons (red, yellow, green).

    Green would be on/ahead of schedule. Yellow would be within 1 week of my estimated date (so 7 days). Red would be behind schedule/more than one week past my estimated date.

    This is what I have so far, but the only thing it is generating is the green button.

    =IF([Actual Mechanical Load Calcs]@row >= [Estimated Mechanical Load Calcs]@row + 7, "Yellow", IF([Estimated Mechanical Load Calcs]@row + 8 <= [Actual Mechanical Load Calcs]@row, "Red", IF([Estimated Mechanical Load Calcs]@row >= [Actual Mechanical Load Calcs]@row, "Green")))

  • horrlind
    horrlind ✭✭
    edited 07/11/23
    Options

    Carson, my criteria has changed and I am having a bit of trouble.

    I am trying to update status symbols with these 4 colors. I need to create an IF statement so that the status bubbles will automatically update when dates change.

    I have 3 columns. Estimated date event will occur, Actual date event will occur, and Status. I am entering the formula into my status column. Would anyone be able to help me generate this formula with this criteria below?

    Green- Completed

    Blue- Pending; On Track

    Yellow- Pending; Overdue by 1 Week or Less

    Red- Not Complete & Overdue by more than 1 Week

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @horrlind

    Just a headsup that I've removed your duplicate posts with the same question and marked all these comments as "no" not answering the question so it stays in the Unanswered list 🙂

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @horrlind,

    Is something like this what you're after?

    =IF([Actual Date]@row < TODAY(), "Green", IF(AND([Actual Date]@row > TODAY(), [Estimated Date]@row > TODAY(), [Actual Date]@row - [Estimated Date]@row <= 0), "Blue", IF(AND([Actual Date]@row >= TODAY(), [Actual Date]@row - [Estimated Date]@row <= 7), "Yellow", "Red")))

    If actual is prior to today: Green (i.e. it should've been finished).

    If both estimated and actual are in the future, and Actual is on or before Estimated: Blue

    If Actual is equal to or later than today AND Actual is 7 or less days greater: Yellow

    Otherwise: Red

    Sample data:

    Hope this helps, but if there is something I've misunderstood or missed something, then post and let's see if it can't be fixed! 😊

  • horrlind
    Options

    @Nick Korna This is kind of what I am going for. The only issue is that I do not have the "actual date" for most of my steps since it has not happened yet. The only actual dates I have will be green since they are completed. The blue, yellow, and red dates do not exist in the "actual" column yet (so I am having to base the colors off of the estimated dates). Does this make sense? Sorry for the confusion!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Ah, something more like this:

    The formula being:

    =IF(AND([Actual Date]@row > [Estimated Date]@row, [Actual Date]@row <> ""), "Red", IF(AND([Actual Date]@row <= [Estimated Date]@row, [Actual Date]@row <> ""), "Green", "Yellow"))

    Is that more in line with what you're after?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!