Need help ignoring blank date columns to use MIN formula

Joanna Collins

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!

hollyconradsmith

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..

hollyconradsmith

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..

Joanna Collins

Thank you!