IF (And statement with date columns?

I have tried several ways including Not(isblank and now using the current as blank fields and ISBLANK, and 0s, now just with quotes... it won't read both coluumns. it is say complete, even if only one column is not blank. i have another column with a similar formula that works GREAT, but it doesn't involve date columns.

=IF(AND([BG Complete]@row = "", [PPHDS Completed]@row = ""), "Pending", "Complete")

Find more posts tagged with

Accepted answers

Dale Murphy

@Deonizia Egan You are using an AND statement, which means ALL conditions must evaluate as true to return the first outcome. The way I read your formula, if BOTH columns are blank, then the item is pending. Otherwise it is complete; meaning if EITHER of those columns is NOT blank, the result is Complete.

Is that your intent? Maybe you need an OR instead?

All comments

Dale Murphy

Is that your intent? Maybe you need an OR instead?

Deonizia Egan

@Dale Murphy Yes that is my intent but that is not the outcome I am getting.

Deonizia Egan

@Dale Murphy it was my understanding that BOTH criteria had to be met in order to return the complete. I have an identical formula that only returns complete when both are met.

=IF(AND([Pending Addendums]@row = "No", Signed@row = 1), "Complete", "Pending")

here it is showing with both blank, and one or the other blank and it gives me pending in the right places and complete in the right place.

Dale Murphy

@Deonizia Egan You understand correctly. In your first formula, "pending" and "complete" were in reverse order, hence a different result. Unless I missed something?

Deonizia Egan

https://community.smartsheet.com/discussion/comment/369578#Comment_369578

Hi Dale,

It took me this long to come back and reevaluate my formula. I didn't quite understand at the time and for some reason what you said clicked now.

YES I needed the OR statement for my second formula. or to reverse the "pending" vs "Complete" would also work with an AND statement, as I have it in my first formula. I never caught that I reversed them on the second one.

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.