Formula for Identifying whether actions are completed on time or late

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

Find more posts tagged with

Accepted answers

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

All comments

Julie Fortney

HI @Rachael Stammers,

Try this formula:

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.