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

FJee

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

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")))))