Date formula - PastDue/Ontime/Ongoing or remain blank ?

Currently using a formula (which works) which states if a column is past due if posted after the target date. Hoping to get help with updating it so that if the date isn't filled in yet it will say "Ongoing" or remain blank.

=IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time")


Thank you very much!

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    Hi @Morris R

    This formula should do what you need, I assumed it was the [Actual End Date] being blank that would trigger the ongoing response. If not you can just rename to [Targeted End Date].

    =IF(ISBLANK([Actual End Date]@row),"Ongoing",IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

    Hope that helps

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    Hi @Morris R

    This formula should do what you need, I assumed it was the [Actual End Date] being blank that would trigger the ongoing response. If not you can just rename to [Targeted End Date].

    =IF(ISBLANK([Actual End Date]@row),"Ongoing",IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

    Hope that helps

    Thanks

    Paul

  • Morris R
    Morris R ✭✭✭✭

    Perfect that worked, Thank you, I see how you added it in there!

    I appreciate the quick and easy response!

  • Morris R
    Morris R ✭✭✭✭

    Hello - Back again. Using your formula has worked great! no issues. Hoping to add another wrinkle?

    Blank: shows "ONGOING" (works)

    Targeted end date < Actual end date = "Past Due" if not its "On time" (works)

    CURRENT USING: =IF(ISBLANK([Actual End Date]@row), "Ongoing", IF(AND([Targeted End Date]@row < ([Actual End Date]@row), Status@row <> "Closed"), "Past Due", "On time"))

    Add in: Completed "EARLY"

    So essentially if a project ends earlier than project "Target Date" then its "EARLY" , if it ends on the same day its "ONTIME" , if its past its "PAST DUE" - In addition to "Actual End date" is blank its "ONGOING"


    Thank you again for your help!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Morris R

    Try adding this statement:

    =IF(ISBLANK([Actual End Date]@row), "Ongoing", IF([Targeted End Date]@row > [Actual End Date]@row, "Early", IF(AND([Targeted End Date]@row < [Actual End Date]@row, Status@row <> "Closed"), "Past Due", "On time")))

    Cheers,

    Genevieve

  • Morris R
    Morris R ✭✭✭✭

    Fantastic thank you that worked!


    I was stuck on order placement.

    Thank you again!

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad I could help! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!