Make AGGREGATE function Google-friendly

Hi all,

Fluff13 helped me create a formula that looks up today's date and then compares text in the adjacent cell for when a destination changes in my travel plan to calculate days. This is the formula that is added below "Time until next destination:"

=AGGREGATE(15,6,C2:C25/(B2:B25<>INDEX(B2:B25,MATCH(TODAY(),C2:C25,0)))/(C2:C25>TODAY()),1)-TODAY()

Here's the original thread.

lEsg0Un.png

Does anyone know how to convert the formula above to something Google-friendly? Edit: Please note that destinations can appear multiple times in the list.

Alternately I came up with a different solution, see the helper formula column (E). If one can make a formula that countsdown when a range of text changes to something else, then I can use INDEX/MATCH to pull the number of days.

Here's a sample sheet: https://docs.google.com/spreadsheets...it?usp=sharing. I also attached the original Excel sheet to the thread.

Does it need to work in Sheets and Excel, or just Sheets?

I think you could use:


=min(filter(C3:C26,(C3:C26>today())*(B3:B26<>INDEX(B3:B26,MATCH(TODAY(),C3:C26,0)))))-today()

Works perfectly. Thanks, rorya!