IF formula to compare due dates to show completion schedule

Maggie Collins

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.

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

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.

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

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.

Maggie Collins

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

Whibley

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

Genevieve P.

Hi @Whibley

Can you post a screen capture showing your columns, but block out sensitive data?