How do I create a dynamic vacation countdown for my Excel calendar?

So I have this Excel calendar that I’ve made that I use at work to track various things, and now I would like to add a “vacation countdown”, that would display the remaining days until the next vacation, to it. Here is an image of a very simplified version with only the parts of the calendar that are relevant to this issue, and below the same in Table Markdown format.

As can be seen from the image and the table I’ve tried to create the “vacation countdown” using the DAYS, XLOOKUP, and TODAY functions, and that worked fine until the value of TODAY > the date of the very first vacation day (i.e. the first vacation day is in the future, and not the past).

Now I could “resolve” the issue by manually entering the new ranges for the functions every time a vacation has passed, but that’s not fun (nor a desirable solution). What I’m looking for is way to get the countdown to show only positive values (i.e. a countdown to only the upcoming vacation and not the ones in the past).

Here is another alternative approach , using TOCOL( ) needs MS365 :

• Formula used in cell F3

=DAYS(@TOCOL(IFS((C4:C36="vacation")*(B4:B36>F2),B4:B36),3),F2)

Another two cents from JvdV Sir, solution proposed is relatively simple and short.

• Formula used in cell F4

=LET(x,FILTER(B4:B36,C4:C36<>""),XLOOKUP(F2,x,x,,1)-F2)

If you have Excel 365 you can use this formula:

=DAYS(TAKE(FILTER(B4:B64,(C4:C64="vacation")*(B4:B64>TODAY())),1),TODAY())

It filters for all future dates with vacation-note and then takes the first result.