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 fi...
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