Task Tracking: Scheduled against actual completion date with IF/AND/IFBLANK and TODAY functions

Hello,

I am trying to set up colour codes to track actual completion status against scheduled completion.

Blank: Not due yet
Blue: Due today or tomorrow
Green: Completed, On time
Red: Overdue, Not completed
Yellow: Completed, Late

I found a formula on this forum (Thank you!) and tweaked it just a little to fit my needs. It works fine, with the exception that tasks that are due on the day show as "Complete" even though the "Actual Input Date" is empty.

=IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row > TODAY(2)), " ", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row >= TODAY(1)), "Blue", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row < TODAY()), "Red", IF([Actual Input Date]@row < [Inputs Due]@row, "Green", IF([Actual Input Date]@row > [Inputs Due]@row, "Yellow")))))

Any suggestions or guidance what is wrong with the formula and what I need to change so I get the desired result?

Thank you!

Felicia

Find more posts tagged with

Accepted answers

All comments

FJee

Thankfully one of my colleagues was able to figure out the issue with my formula:

=IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row > TODAY(1)), " ", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row >= TODAY()), "Blue", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row < TODAY()), "Red", IF([Actual Input Date]@row < [Inputs Due]@row, "Green", IF([Actual Input Date]@row > [Inputs Due]@row, "Yellow")))))

Maybe this will come in useful to someone else :)

FJee

This is a bit of a monologue, but maybe someone needs a similar solution as I did. When I tested the function, I got "blank" when the due and actual date were identical.

I was able to fix that by adding an = in the "Green" sequence. Just in case some stumbles across the same issue.

=IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row > TODAY(1)), " ", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row >= TODAY()), "Blue", IF(AND(ISBLANK([Actual Input Date]@row), [Inputs Due]@row < TODAY()), "Red", IF([Actual Input Date]@row <= [Inputs Due]@row, "Green", IF([Actual Input Date]@row > [Inputs Due]@row, "Yellow")))))

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.