Need help ignoring blank date columns to use MIN formula

I am trying to first convert a text string into a date to use in a MIN formula. Some of my cells will not have dates, so it is returning a "invalid data type" error in those cells.
HELPM3 formula:
=DATE(VALUE(MID([SASH METAL OFFLINE]@row, 7, 4)), VALUE(LEFT([SASH METAL OFFLINE]@row, 2)), VALUE(MID([SASH METAL OFFLINE]@row, 4, 2)))
I have three columns similar to the HELPM3 that I am converting
I would like to use those 3 columns to find the earliest date, but ignore the "invalid data type" error or blanks. In this First Offline column.
Formula for First Offline column:
=MIN([HELPG3]@row, [HELPS3]@row, [HELPM3]@row)
Right now it is only working if all of my "help" columns have dates, but often 1 or 2 of these columns will be blank.
Any help would be appreciated!
Best Answer
-
Hi @Joanna Collins ,
Try using "IFERROR" to cancel out those invalid data types.
https://help.smartsheet.com/function/iferror
The you can use if(not(isblank(value@row to apply the rest of your formula to only the cells with real data..
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
Answers
-
Hi @Joanna Collins ,
Try using "IFERROR" to cancel out those invalid data types.
https://help.smartsheet.com/function/iferror
The you can use if(not(isblank(value@row to apply the rest of your formula to only the cells with real data..
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 151 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!