Hi Marie

My suggestion assumes that your Dates are in column A, Time in column B and Tide Level in column C.

And if your input date is in cell E2, and Tide Level in cell F2, then try this array formula (must be confirmed with Ctrl+Shift+Enter)

If you need more than 5000 rows then adjust the formula accordingly.

="From "&TEXT(SMALL(IF($A$2:$A$5000=E2,IF($C$2:$C$5000 < F2,$B$2:$B$5000)),1),"h:mm")&" to "&TEXT(SMALL(IF($A$2:$A$5000=E2,IF($C$2:$C$5000 > F2,IF($B$2:$B$5000 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 < F2,$B$2:$B$241)),1),$B$2:$B$5000))),1),"h:mm")&", and from "&TEXT(SMALL(IF($A$2:$A$5000=$E$2,IF($C$2:$C$5000 < $F$2,IF($B$2:$B$5000 > =SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 > F2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 < F2,$B$2:$B$241)),1),$B$2:$B$241))),1),$B$2:$B$5000))),1),"h:mm")&" to "&TEXT(SMALL(IF($A$2:$A$5000=$E$2,IF($C$2:$C$5000 > $F$2,IF($B$2:$B$5000 > SMALL(IF($A$2:$A$241=$E$2,IF($C$2:$C$241 < $F$2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 > F2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 < F2,$B$2:$B$241)),1),$B$2:$B$241))),1),$B$2:$B$241))),1),$B$2:$B$5000))),1),"h:mm")&IFERROR(", and from "&TEXT(SMALL(IF($A$2:$A$5000=$E$2,IF($C$2:$C$5000 < $F$2,IF($B$2:$B$5000 > SMALL(IF($A$2:$A$241=$E$2,IF($C$2:$C$241 > $F$2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=$E$2,IF($C$2:$C$241 < $F$2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 > F2,IF($B$2:$B$241 > SMALL(IF($A$2:$A$241=E2,IF($C$2:$C$241 < F2,$B$2:$B$241)),1),$B$2:$B$241))),1),$B$2:$B$241))),1),$B$2:$B$241))),1),$B$2:$B$5000))),1),"h:mm"),"")

Vidar