Formula for Identifying whether actions are completed on time or late

Rachael Stammers

All,

This is driving me crazy! I have written a formula to show

• if the due date is blank, leave the cell blank
• if the completion date is before or equal to the due date = on time
• if the completion date is after the due date = late
• if the completion date is blank and the completion date is before today = late

The formula I'm using is:

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

and I've tried this

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

However, in the cells where the due date overdue and the completion date has not been identified, I am getting the result "On Time" (See F 1 Mitigation Due Date - 02/08/22 and 08/19/22)

All help is GREATLY appreciated.

Rachael

Julie Fortney

HI @Rachael Stammers,

Try this formula:

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

I think there were a few things going on:

• I have used a logical expression like [F 1 Mitigation Due Date]@row = "" with success before, but I had to change it to ISBLANK to make this formula work.
• I moved the if the completion date is blank and the due date is before today = late criterion to be the second IF statement. I think Smartsheet was interpreting a blank completion date to somehow be less than or equal to the due date. Weird.
• If you need to set multiple criteria for an IF statement, you can use IF(AND.

I hope that helps!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Julie Fortney

HI @Rachael Stammers,

Try this formula:

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

I think there were a few things going on:

• I have used a logical expression like [F 1 Mitigation Due Date]@row = "" with success before, but I had to change it to ISBLANK to make this formula work.
• I moved the if the completion date is blank and the due date is before today = late criterion to be the second IF statement. I think Smartsheet was interpreting a blank completion date to somehow be less than or equal to the due date. Weird.
• If you need to set multiple criteria for an IF statement, you can use IF(AND.

I hope that helps!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Rachael Stammers

@Julie Fortney Thank you so much. It worked. It wasn't working to begin with, but that was my error, I wasn't adding the ) at the end of some pieces of the formula.

Thank you so much for your help.

Julie Fortney

@Rachael Stammers That's great to hear! You're very welcome.