Forum Discussion

LindsayB2015's avatar
LindsayB2015
Copper Contributor
Sep 02, 2024

Finding a date and time between 2 dates

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

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Sep 06, 2024

    Hi LindsayB2015 

     

    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)
)

     

Resources