Formula Help with Past Dates

Options

Hi there, I am trying to get my status row to say Late when the following are met:

1.) Due Date is in the past

2.) Due Date happens in the past to Date Completed

3.) Date Completed is Blank

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @mragans23,

    Something along these lines?

    =IF(AND([Due Date]@row < TODAY(), [Date Completed]@row = ""), "Late", IF([Date Completed]@row > [Due Date]@row, "Late", "On Time"))

    If the due date is in the past and date completed is blank, Status is "Late". If the Completed Date is later than the Due Date, then Status is "Late", otherwise "On Time".

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just let us know!

Answers

  • mragans23
    Options

    I started with this, but do not have all of the argument in:

    =IF(ISBLANK([Date Completed]@row), "Late", IF(AND([Due Date]@row < TODAY()), "Late", "On Time"))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @mragans23,

    Something along these lines?

    =IF(AND([Due Date]@row < TODAY(), [Date Completed]@row = ""), "Late", IF([Date Completed]@row > [Due Date]@row, "Late", "On Time"))

    If the due date is in the past and date completed is blank, Status is "Late". If the Completed Date is later than the Due Date, then Status is "Late", otherwise "On Time".

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just let us know!

  • mragans23
    Options

    Thank you thank you, that works great.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!