If cells in a range are blank, return blank; otherwise show max date.

Jasmine Norris

Hello, I am new to Smartsheet and need assistance on this formula. Columns labeled 1-5 will  have different dates input into them depending on when the appropriate file is collected. I want the Completed Date column to do two things:

1. If any cells in the row for columns 1-5 are blank, I need the Completed Date column to remain empty.
2. If all dates are filled in columns 1-5, I need the Completed Date column to show the max date.

Not sure if this can be done, I have been playing around with formulas using ISBLANK and OR so far, but I can’t get it quite right. Thank you.

Toufong Vang

Hi, Jasmine

Try, the following formula in your column named Completed Date. 

=IF(COUNTIF([1]@row:[5]@row,@cell<>"")=5,MAX([1]@row:[5]@row),"")

Since Completed Date is to be blank unless all 5 dates are filled-in, you can use COUNTIF() to evaluate the range.

Toufong Vang

Hi, Jasmine

Try, the following formula in your column named Completed Date. 

=IF(COUNTIF([1]@row:[5]@row,@cell<>"")=5,MAX([1]@row:[5]@row),"")

Since Completed Date is to be blank unless all 5 dates are filled-in, you can use COUNTIF() to evaluate the range.

Jasmine Norris

https://community.smartsheet.com/discussion/comment/294077#Comment_294077

This worked, thank you so much!