How do I show the number of days left before the next date listed in a range of dates?

Question

I have a range of deadlines, some have passed, some are in the future (A:4-A:8).

I'd like to create a formula in a single cell that takes that range, finds the next future date and returns the days left until then.

For example, if today is August 15, 2023 (8/15/2023) and the range of dates is 8/13/2023, 8/14/2023, 8/15/2023, 8/16/2023 and 8/17/2023, I want it to ignore the past and present day as well as the other future days and return 1 (i.e. 1 day until next deadline).

Example: 8/13/2023 (past) 8/14/2023 (past) 8/15/2023 (today) 8/16/2023 (next deadline) 8/17/2023 (future deadline)

= 1 (days left until next deadline)

I've tried several formulas that don't seem to work and I'm out of ideas.

Answer 1

This seems to work:

=DATEDIF(TODAY(),INDEX(A:A,MATCH(TODAY(),A:A,1)+1),"D")