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
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!