IF, date with status column

I have a sheet with two date columns- Completion Date and Due Date.

I also have a Completed on time column with Red, Yellow and Green Harvey balls.

I want the ball to appear green if the Completion Date is before the Due Date and the ball to be red if the Completion Date is after the Due date.

I originally planned to do this with conditional formatting (highlighting the row instead of Harvey balls) but it doesn't seem to be an option.

Please help!

Find more posts tagged with

Formulas

Accepted answers

All comments

Brian Wilson DC

@RGarrels You will want to use a formula like this:

=IF([Completion Date]@row < [Due Date]@row, "Green", IF([Completion Date]@row > [Due Date]@row, "Red", " "))

If you wanted to add in yellow for having it "just on-time" then you could add in another IF statement, such as:

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

That will produce:

I hope this helps!

andrew.taylor37161

Hi, hope this helps

You can write a conditional formula in you result column (the one you are using the balls in)

along the lines of

=IF(ISBLANK([Completion Date]@row), "", IF([Completion Date]@row < [Due Date]@row, "Green", IF([Completion Date]@row > [Due Date]@row, "Red", "")))

right click on the cell and change to a column formula

This will leave cell blank if there is no date in the completion date, Red ball if it is after the due date and Green ball if before.

Hope this helps

andrew.taylor37161

also, I am assuming that you have the result column as a "Symbols" format

andrew.taylor37161

Further, if you want to create a conditional format to indicate if the due date is in the past and no completion date has been added, you could create a conditional format that changes to row or cell to an identifying colour (yes, that is how we spell it)

Create a new Conditional Format "If Due Date is in the past, and Completion Date is blank, then apply "(choose format)" to the entire row (or the cell column you want formatted). You may need to create the format "in the past" and after creating it, click on the dropdown icon in the formular and "Add Condition" for the Completion Date is blank" part

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.