IF formula to compare due dates to show completion schedule

Options
edited 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?

Tags:

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭
Options

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.

• Options

Thanks Kelly and Victoria!! These both worked for what I'm looking to accomplish. Much appreciated

• Options

Hello I'm applying this to course completion dates and it works if I omit the is this a date check.

Some of our courses weren't assigned a due date in error - the check if the due date is a date in the first query returns #NAME?

Not sure what I've missed as copied exact just changed the return values:

=IF(ISDATE(M780),IF(ISBLANK(N780),IF(M780>=TODAY(),"Due","Late"),IF(N780>M780,"Late",IF(N780=M780,"Due",IF(N780<M780,"On Time")))),"No due date")