If cells in a range are blank, return blank; otherwise show max date.
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:
- If any cells in the row for columns 1-5 are blank, I need the Completed Date column to remain empty.
- 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.
Best Answer
-
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.
Answers
-
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.
-
This worked, thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!