IF formula to compare due dates to show completion schedule

Maggie Collins · 02/16/23

Hello all!

I have a formula that is stumping me - I am looking to compare a target due date versus the actual completion date. I have a formula that is partially working, but am running into issues when I use the term "ongoing" on the target due date column (see Project 4 in the below example).

This is what I'm trying to solve for:

If the Target Due Date is "Ongoing" and the Completion Date is blank, I'd like the Completion on Time (4th column) column to say On Time

Here is the formula that I am working with:

=IF(AND(ISBLANK([Completion Date]@row), [Target Due Date]@row >= TODAY()), "On Time", IF([Completion Date]@row > [Target Due Date]@row, "Late", IF([Completion Date]@row = [Target Due Date]@row, "On Time", IF([Completion Date]@row < [Target Due Date]@row, "Early"))))

Any chance someone could help incorporate the ongoing section into the formula?

Thanks in advance!!

Kelly P. · 02/16/23

Try this; it works for me:

=IF(AND(NOT(ISDATE([Target Due Date]@row)), ISBLANK([Completion Date]@row)), "On Time", IF(AND(ISBLANK([Completion Date]@row), [Target Due Date]@row >= TODAY()), "On Time", IF([Completion Date]@row > [Target Due Date]@row, "Late", IF([Completion Date]@row = [Target Due Date]@row, "On Time", IF([Completion Date]@row < [Target Due Date]@row, "Early")))))

Hope this helps!

Victoria_Indimar · 02/16/23

Hello!

I updated your formula to check if Target Due Date is a date first.

=IF(ISDATE([target due date]@row), IF(ISBLANK([completion date]@row), IF([target due date]@row >= TODAY(), "On Time", "Late"), IF([completion date]@row > [target due date]@row, "Late", IF([completion date]@row = [target due date]@row, "On Time", IF([completion date]@row < [target due date]@row, "Early")))), "On Time")

Commented below to break it down...

=IF(ISDATE([target due date]@row), // check if date

IF(ISBLANK([completion date]@row), // if target is a date, then check if completion is blank

IF([target due date]@row >= TODAY(), "On Time", "Late"), // when target is a date + completion is blank, check target. If Target is greater or equal than today On Time, if less than it's Late.

IF([completion date]@row > [target due date]@row, "Late", // when target is date but completion is not blank, compare Completion to Target. If Greater = Late

IF([completion date]@row = [target due date]@row, "On Time", // If Equal = On Time

IF([completion date]@row < [target due date]@row, "Early")))), // If Less = Early

"On Time") //Target is not a date so it is On Time. NOTE: This assumes you would never have a Completion without having a Target but if it does, it's considered On Time)

Hope this works for you! Let us know.

Kelly P. · 02/16/23