-1

I have a list of dates on column A. Each day I need to automatically put the date of today or the next closest date on cell B1.

20/8/2018
6/9/2018
25/9/2018
28/9/2018
11/10/2018
30/10/2018
31/10/2018
10/11/2018
15/11/2018
15/12/2018

I already tried using MATCH but it returns and index value. I could use INDEX to get the date corresponding to that index value.

=INDEX(A1:A10,MATCH(TODAY(),A1:A10,1)+1)

Is there a simpler/ straight forward way to achieve the same result?

0

Another method to get the same result is, but I don't think that it could be simpler or straight forward but maybe it could me "more logical" for a "non spreadsheet thinker"

=ARRAY_CONSTRAIN(FILTER(A1:A10,A1:A10>=TODAY()),1,1)

  • FILTER returns an array of values that that met the condition.
  • ARRAY_CONSTRAIN reduce the size of the array, in this case, returns a single value. The same could be achieved by using INDEX.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.