Help w/multiple AND NOT ISBLANK date field formula

jmo

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.

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

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 !