How can I get the min() date which is >= today()

[Philipp Ruf]

Hello,

in Col "E" I want the min date which isnt below today from the range O: X

Atm I have this: =TODAY() + array_constrain(MIN(FILTER(O2:X2; O2:X2>=0));1;1)

But array_constrain(MIN(FILTER(O2:X2; O2:X2>=0));1;1) results in 0 if there is no result. So TODAY() + 0 = today. But I want it to be empty if there is no result from array_constrain.

If you want you can free edit in this copy 

https://docs.google.com/spreadsheets/d/1ug9riGLgVWi0avbJ-MA_xeRi8jlnAee1kBGpeKJMMdw/edit?usp=sharing

Thanks in advance

PR

[Keith Andersen]

Philip,

I don't think you need array_constrain or filter because MIN will give you a single return

Therefore, =MIN(O2:X2) will give you a result of the smallest number

So now test that result to get your desired output

 =IF( MIN(O2:X2) <> 0, MIN(O2:X2), "" )

If the result is does not equal 0, it will output the min value. If it equals 0 it will be blank.

Now, combine this with adding TODAY() if the result is greater than 0

=IF( MIN(O2:X2) <> 0, TODAY + MIN(O2:X2), "" )

If the result does not equal 0 it will give you a date equal to TODAY() plus the resulting min number. If the result is 0, it will be blank.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ad80d4fe-d08f-41ab-9469-a1c3d0ef21bdn%40googlegroups.com.

[Keith Andersen]

Sorry... forgot some brackets after TODAY

=IF( MIN(O2:X2) <> 0, TODAY()+ MIN(O2:X2), "" )

[Keith Andersen]

And, to include a 0 result this being today try....

=IF( MIN(O2:X2) >= 0, TODAY() + MIN(O2:X2), "" )

[Keith Andersen]

Philip,

My apologies, my formula doesn't take into account negative numbers.

Going back over your request so I don't miss anything....

You said:

(First)

in Col "E" I want the min date which isnt below today from the range O: X

This means TODAY is acceptable which by your formula would output 0

You said:

(Second)

But array_constrain(MIN(FILTER(O2:X2; O2:X2>=0));1;1) results in 0 if there is no result. So TODAY() + 0 = today. But I want it to be empty if there is no result from array_constrain.

In my testing, 0 only occurs if 0 is the result of the formula - Subtracting TODAY from TODAY.

So, to EXCLUDE a 0 result change your formula from >= to > ....this will solve PART of the problem.

array_constrain(MIN(FILTER(O2:X2; O2:X2 > 0));1;1) 

If all the results were 0 or UNDER (a negative number) the output result of the formula is #NA.

Therefore, to handle the #NA simply wrap your formula in IFNA() like so...

 

=IFNA( TODAY() + ARRAY_CONSTRAIN(MIN(FILTER(O2:X2, O2:X2  > 0)),1,1) )

However, the MIN function returns a single value which is what you are attempting to do with ARRAY_CONSTRAIN(). So you can remove it.

=IFNA( TODAY() + MIN( FILTER(O2:X2, O2:X2  > 0) ) )

That should do it nicely.

If it doesn't work let me know but I tested it fine.

Cheers,

Keith 

[Philipp Ruf]

Thank you Keith!

Your solution

=IFNA( TODAY() + MIN( FILTER(O2:X2, O2:X2  > 0) ) )

worked like a charm. I was about to script it because if forgot the IFNA.

[Keith Andersen]

👍 You're welcome. Glad to be of service.

Cheers

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/67c9b411-f7e9-44a9-878b-783e8a43967dn%40googlegroups.com.