Help w/multiple AND NOT ISBLANK date field formula

Hi team - I have 6 date column fields that are automatically formatted (record a date) when a Status field changes.

Let's just call those columns:

Date1
Date2
Date3
Date4
Date5
Date6

I created a check box "helper column" that I'd like to create a formula that looks across those fields to determine if ALL of them have a date in them. If they do, check the box - if they don't then leave the box unchecked.

I struggle with the correct "stringing" of the formula variables to compute the check/uncheck output.

Pretty sure I need to use:

IFERROR
IF
AND
NOT
ISBLANK

Any ideas how to string this together?

Thanks.

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @jmo

Are your date fields contiguous (all side by side?). I'm assuming they are. If not, each date field will have to be added to the COUNTIFS individually

=IF(COUNTIFS([Date1]@row:[Date6]@row, ISDATE(@cell))=6, 1)

Will this work for you?

Kelly

All comments

Kelly Moore

Hey @jmo

Are your date fields contiguous (all side by side?). I'm assuming they are. If not, each date field will have to be added to the COUNTIFS individually

=IF(COUNTIFS([Date1]@row:[Date6]@row, ISDATE(@cell))=6, 1)

Will this work for you?

Kelly

jmo

The dates are not contiguous - there are a couple of other columns between each date field.

Kelly Moore

If there are no other date columns in between the desired columns, the formula will still work as it's looking for dates. The range should encompass all of your desired date columns.

Kelly

jmo

I wound up just running a check between all column (mostly dates) from my "start" column thru the "end" column.

It appears to be work ok for now.

Appreciate the assist, @Kelly Moore !

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.