IF statement help

horrlind

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

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

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

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

=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

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

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.

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

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

@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

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.