Formula to change text in a cell, if the date has passed the date set in another cell, ignore blanks

Hello all, I'm trying to create a formula that will check the target date against the date completed, and return "Late" if it's late and "On Time" if its not. This formula works as intended except that it lists all blanks as "Late". I have limited experience with formulas, and only the simpler ones.

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

I found another formula that properly omitted the blanks, but it wasn't reading the dates correctly and I'm not sure how to make the above formula stop returning "Late" for blanks. The above formula properly reacts to the date, but it counts all blanks.

Any help would be appreciated, and my apologies for having to Frankenstein my way through these formulas.

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

You are really close. So the first part of your formula says if the End Date is Blank and the Target Date is less than Today, "Late". What do you want the blanks to say? So how about this:

So to break down this formula - look at it in pieces. And you must put these pieces in the order you want them to operate. So for instance, if I put the first IF statement at the beginning, the second IF statement would be the one with the AND, but because the criteria had already been met in the first statement (the End Date being blank), it would completely ignore the other. Does this make sense?

So in order your formulas are:

IFERROR — If there is an error, we are currently having it return "". So if it meets none of the criteria it will leave it blank
IF(AND([End Date]@row ="", [Target Date]@row < TODAY()) — If the end date is blank, and the target date is before today, return "Late"
IF([End Date]@row ="", "" — If the End Date is blank (and the Target Date is blank or Today or later), return ""
IF([End Date]@row > [Target Date]@row, "Late" — If the End Date is greater than the Target Date, return "Late"
IF([End Date]@row <= [Target Date]@row, "On Time" — If the End Date is less than or equal to the Target Date, return "On Time"

The full formula together is this:

=IFERROR( IF(AND([End Date]@row ="", [Target Date]@row < TODAY()), "Late", IF([End Date]@row ="", "", IF([End Date]@row > [Target Date]@row, "Late", IF([End Date]@row <= [Target Date]@row, "On Time")))),"")

I might have misunderstood some of your formula, so please tweak as necessary to meet your needs. For me it is SUPER helpful to look at it in pieces before I put it all together.

If you still need help, feel free to throw some time on my calendar. Link below.

HWESTUSSC

Michelle, Thanks a lot for taking the time to explain how you set it up and why! I do appreciate it, there's still so much to learn.. and it's all so basic to those that already know.

I did manage to cobble together something that seems to work. I just tried what you pasted and it still populates all the rows with "Late" if the field is empty…

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

For whatever reason, this above formula seems to be working perfectly. All the blanks remain blank, and Late and On Time are properly populated.

Thanks again for taking the time to look into this with me!

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.