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
Check out the Formula Handbook template!