IF formula to compare due dates to show completion schedule

Maggie Collins
Maggie Collins ✭✭✭
edited 02/16/23 in Formulas and Functions

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!!



Tags:

Best Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓

    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
    Victoria_Indimar ✭✭✭✭✭
    Answer ✓

    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.

Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭
    Answer ✓

    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
    Victoria_Indimar ✭✭✭✭✭
    Answer ✓

    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.

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

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

  • Hi @Whibley

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!