If date completed is later/before/on date then enter text

This seems like it would be a really easy formula but I can't find anything for it.

If [date completed]1 is later than [deadline]1 then enter "Late"

if [date completed]1 is before or on [deadline]1 then enter "On Time"

Thanks so much!

Best Answer


  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭

    Try this formula

    =IF([date completed]@row >[deadline]@row, “Late”, IF([date completed]@row <= [deadline]@row, “On Time”))

  • Chelsea Duncan
    edited 11/02/21

    Yep, it worked. And of course, simple. Why do you use @row vs the actual row number?

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭

    Using @row is considered best practice and is supposed to improve performance of the formula calculations, and make loading/saving sheets faster.

    It also makes it easy to convert the cell formula to a column formula.

  • Chelsea Duncan
    edited 11/04/21

    @Jason Duryea

    There's something with this that I didn't consider. The formula still returns "on time" even if there is no date completed entered, which means it could still be in work when it's overdue. Can I have it to where it enters Late if the analysis due date has passed and there is no date in analysis date completed?

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭

    Now you're getting fancy...😀

    You would need to add another condition to the nested IF statement.

    AND(ISBLANK([date completed]@row), [deadline]@row < TODAY())

    It would look like this...

    =IF(AND(ISBLANK([date completed]@row), [deadline]@row < TODAY()), "Late", IF([date completed]@row >[deadline]@row, “Late”, IF([date completed]@row <= [deadline]@row, “On Time”)))

  • Chelsea Duncan
    edited 11/06/21

    Lol, I am a bit fancy!

    So I'm getting unparseable. To summarize: If the analysis due date is in the past, and the date completed is blank, then "Late". If the analysis due date and the date completed is in the past then "Late". If the date completed is equal to or before the date completed, then "On Time".

    To compare, the previous formula, which worked, is:

    =IF([h-analysis date completed]@row >[h-analysis due date]@row, “Late”, IF([h-analysis date completed]@row <= [h-analysis due date]@row, “On Time”)

    Adjusted for the blank date completed, formula is:

    =IF(AND(ISBLANK([H-analysis date completed]@row), [H-analysis due date]@row < TODAY()), "Late", IF([H-analysis date completed]@row >[H-analysis due date]@row, “Late”, IF([H-analysis date completed]@row <= [H-analysis due date]@row, “On Time”)))

    Any ideas why it's not working?

    Thank you

  • I've been known to be a little fancy lol.

    This is what worked and I have no idea why:

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

    I would love to know why this one worked cause the one you did returned unpareseable.

    Thank you.

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Answer ✓

    The only difference I see are the column names that you used, which I believe are case sensitive...maybe that is the difference.

  • Rachael Stammers
    Rachael Stammers ✭✭✭✭

    Hi Everyone,

    I'm struggling with a similar formula coming back as #INCORRECT ARGUMENT :(

    My column Names are shown in the image

    And the formula I'm using below:

    =IF(AND(ISBLANK([F2 Mitigation Completion Date]@row, [F 2 Mitigation Due Date]@row < TODAY()), "Late", IF([F2 Mitigation Completion Date]@row > [F 2 Mitigation Due Date]@row, "Late", IF([F2 Mitigation Completion Date]@row <= [F 2 Mitigation Due Date]@row, "On Time"))))

    All help greatly appreciated

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!