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:

  • IF
  • AND
  • NOT

Any ideas how to string this together?


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!