Formula that will display the lowest value from two data sets (with the highest value).

EmilyH

Hello Smartsheet community, I need some help fixing a formula.

The goal = Display the lowest value between (the highest value between 1A and 1B) and (the highest value between 2A and 2B).

The formula I created is:

=MIN(COLLECT(MAX(COLLECT([DATE 1A]@row, [DATE 1A]@row, @cell <> ""), COLLECT([DATE 1B]@row, [DATE 1B]@row, @cell <> "")), MAX(COLLECT([DATE 2A]@row, [DATE 2A]@row, @cell <> ""), COLLECT([DATE 2B]@row, [DATE 2B]@row, @cell <> "")), @cell <> ""))

Note: It works for scenarios A, C and E. However, for scenario B the date should be 05/15/20. For scenario D the date should be 02/20/23.

Thank you so much for your help.

Emily

NOTE: All date columns have a Date format.

Andrew Dehyd

Hi @EmilyH,

At first I tried

=MIN(MAX([Date 1A]@row, [Date 1B]@row), MAX([Date 2A]@row, [Date 2B]@row))

and that almost worked... but then I had "#INVALID DATA TYPE" when 1A and 1B were both empty (or 2A and 2B were both empty).

Here's another way that might work, although it's a bit messy - it checks if the 1A:1B pair is empty, and also 2A:2B pair. Someone else might be able to come up with something much neater?

=IF(ISDATE(MAX([Date 1A]@row, [Date 1B]@row)), IF(ISDATE(MAX([Date 2A]@row, [Date 2B]@row)), MIN(MAX([Date 1A]@row, [Date 1B]@row), MAX([Date 2A]@row, [Date 2B]@row)), MAX([Date 1A]@row, [Date 1B]@row)), MAX([Date 2A]@row, [Date 2B]@row))

That's not easy to read: it might work but doesn't seem like it should need a formula that complicated. I'd be interested in finding out the correct way of doing this too!!