Reporting Tasks Late or On Time - issue with Date Completed is blank incorrect response

Any help or suggestion with formula to that will be used to report activity as completed on time (on or before “Late Date”) based on the Date Completed. Everything is working correctly except when Late Date is in past and Date Completed is blank (because task is not complete). The logic is calculating as “On Time”. It should be Late.

I changed to formula below thinking that since Date Completed is Blank (therefore not greater than Late Date) it should report as Late.

Note the first IF statement is so that I do not report on future events and want the Completed on Time field to be blank until we hit reporting date.

Current:

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

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

Find more posts tagged with

Accepted answers

Bassam Khalil

To address the issue you're experiencing with your Smartsheet formula, especially with handling tasks where "Date Completed" is blank and ensuring they are reported as "Late" if the "Late Date" is in the past, you need a formula that correctly interprets blank "Date Completed" fields. The problem with your current formula is that it doesn't explicitly handle the case where "Date Completed" is blank, leading to incorrect categorization.

Here is an improved formula that should address your needs. This formula checks:

If the "Late Date" is in the future, it leaves the "Completed on Time" field blank.
If "Date Completed" is blank and "Late Date" is in the past, it marks the task as "Late".
If "Date Completed" is not blank, it then checks if it is before or after the "Late Date" to mark as "On Time" or "Late" respectively.

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

Explanation of the formula:

The outer IF checks if the "Late Date" is in the future; if so, it leaves the "Completed on Time" field blank.
The first IF inside checks if "Date Completed" is blank. Then it checks if the "Late Date" is before today's date; if so, it marks the task as "Late". If "Late Date" is not before today (meaning it's either today or somehow the formula got past the outer check for future dates), it doesn't set a value (this case should technically not occur due to the outer IF).
The second IF inside is only evaluated if "Date Completed" is not blank, comparing "Date Completed" to "Late Date" to determine if the task is "Late" or "On Time".

This formula should ensure that tasks with no "Date Completed" are properly marked as "Late" if the "Late Date" has passed, addressing the core issue you were facing.

All comments

Bassam Khalil

Here is an improved formula that should address your needs. This formula checks:

If the "Late Date" is in the future, it leaves the "Completed on Time" field blank.
If "Date Completed" is blank and "Late Date" is in the past, it marks the task as "Late".
If "Date Completed" is not blank, it then checks if it is before or after the "Late Date" to mark as "On Time" or "Late" respectively.

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

Explanation of the formula:

The outer IF checks if the "Late Date" is in the future; if so, it leaves the "Completed on Time" field blank.
The first IF inside checks if "Date Completed" is blank. Then it checks if the "Late Date" is before today's date; if so, it marks the task as "Late". If "Late Date" is not before today (meaning it's either today or somehow the formula got past the outer check for future dates), it doesn't set a value (this case should technically not occur due to the outer IF).
The second IF inside is only evaluated if "Date Completed" is not blank, comparing "Date Completed" to "Late Date" to determine if the task is "Late" or "On Time".

This formula should ensure that tasks with no "Date Completed" are properly marked as "Late" if the "Late Date" has passed, addressing the core issue you were facing.

BobS7

Thanks Bassam you nailed it. I have put in my sheet run several tests and seems to be working as expected. Will be able to replicate this in multiple other sheets I need to create for HR tracking. I really appreciate the detailed explanation and will save this as the logic may very well be needed in future sheets.

Kind Regards, Bob

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.