Use of IF and AND with Dates

Hello Smartsheet Community, I would appreciate your help to get this formula working. I am trying to fill the Evaluation Status column with Current or Due based on the following criteria. if today's date is after the anniversary date AND the Date Completed is empty, complete with DUE; otherwise, CURRENT.

=IF(AND(TODAY() > [ANNIVERSARY DATE]@row,isblank([DATE COMPLETED]@row]), "DUE", "CURRENT")

Thank you in advance for your help!

Stephany

Find more posts tagged with

Accepted answers

Dan W

You should be able to paste this in and it will work. You added a @row to TODAY()

=IF(AND([ANNIVERSARY DATE]@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

I also changed the >to <.

To be clear if the Anniversary date is lets say 5/18 you would want it to say current.

But if the date was 5/15 you would want due?

All comments

Dan W

You are close. The IF statement would be If date equals, Not If Today equals. Also you can use "" to signify blank.

=IF(AND(AnnDate@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

Stephany Gonzales

Hello Dan,

Thank you! I revised it with your notes and still shows the #UNPARSEABLE message

=IF(AND([ANNIVERSARY DATE]@row>TODAY()@ROW,[DATE COMPLETED]@ROW=""),"DUE","CURRENT)

Dan W

You should be able to paste this in and it will work. You added a @row to TODAY()

=IF(AND([ANNIVERSARY DATE]@row < TODAY(), [Date Completed]@row = ""), "DUE", "CURRENT")

I also changed the >to <.

To be clear if the Anniversary date is lets say 5/18 you would want it to say current.

But if the date was 5/15 you would want due?

Stephany Gonzales

It worked! Thank you Dan 😀 I appreciate your help 👏

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.