G'day brains trust,
I am hoping someone can help me. I am trying to create a formula to find a berthing window for a ship. When I enter a time and date in A1&2 I need it to tell me what window it fits into (A5:B15) between Open and Close. The closest I can get is by splitting the open and close times into separate lists (A18:B23)&(A26:B30) and than using the min and max to get the times in D5 & D6. This works if the searched time is within a window, but my problem is that this doesn't take into account the open and close conditions. So if I was to search for the time 23:30/24 which is outside a window, it will tell me 1815/24 until 1130/25, but I want it to tell me the next available window which is 0700-1130/25.
FYI this isn't the final layout, I am trying to make a dashboard with all the data on a separate sheet, but moving it all to one sheet made it easier to explain to you guys.
Appreciate any help I can get. Thanks in advance.
Cheers
Lindsay
With MINIFS and data formatted as Table named Table1
Assuming you run Excel 2021 or 365
Open:
=LET(
open?, MINIFS(Table1[Window], Table1[Status],"open", Table1[Window],">=" & B2+B1),
window, IF(open?, open?, "No slot found"),
IF(COUNT(B1:B2) < 2, "", window)
)Close:
=IF(ISTEXT(E1), "",
MINIFS(Table1[Window], Table1[Status],"close", Table1[Window],">=" & E1)
)