Finding the smallest date in a string of dates contained in a single cell

Stephen Ivy

Hi all,

I've run into a problem that I just can't seem to solve with smartsheets formulae. I have a date input cell which usually has only one input, but can have many (10+). I want to highlight this cell if any date in the cell is today or in the past. So in theory I just need to find the smallest date and compare it to today's date. However, the fact that the cell can be either a date or a string (of dates) with an unknown amount of characters depending on the amount of dates in the cell has proven difficult. Does anyone have any clever ideas to solve this?

For reference, the dates are in the format MM/DD/YY and can be separated by one or multiple spaces or a line break. I've been able to convert multiple dates with varying delimiters to a string in the format "MM/DD/YY,MM/DD/YY,..." but I don't really know where to go from here.

Thanks!

Paul Newcome

The first thing to do would be to determine what the maximum number of dates in a single string would be. Then you would need to insert that many helper columns to be able to parse each date out into its own cell. From there you can use a MIN to grab the earliest date from the helper columns on that row and nest it in an IF.