Cross reference match formula

AngB789

I am looking to cross reference Sheet A to pull in Time slots from Sheet B.

Criteria I need to have matching from Sheet A to pull from Sheet B to determine the slot number or multiple numbers.

1. Start Time
2. End Time

There could be multiple Slot Numbers so it would need to be a multi option that I could use later to separate in a report (time slots available).

Sheet A: Need Slot Numbers

Sheet B:

jmyzk_cloudsmart_jp

Hi @AngB789

I hope I understand your needs correctly. It sounds like you want to match the Start and End times in Sheet A with the corresponding time slots in Sheet B and pull multiple Slot Numbers into a single cell or a multi-select dropdown format.

To make this comparison more manageable, I added helper columns [S] and [E] to convert the Start Time and End Time into numerical values. This allows for easier filtering of the relevant slots.

https://app.smartsheet.com/b/publish?EQBCT=9c5c9e6b158b4ee9b9a950d516a5c642

[Slot Number] =JOIN(COLLECT({Sheet B: Time Slots : Slot Number}, {Sheet B: Time Slots : S}, >=S@row, {Sheet B: Time Slots : E}, <=E@row), CHAR(10))

[S] =VALUE(LEFT(TIME([Start Time]@row, 1), 2)) + VALUE(RIGHT(TIME([Start Time]@row, 1), 2)) / 60
[E] =VALUE(LEFT(TIME([End Time]@row, 1), 2)) + VALUE(RIGHT(TIME([End Time]@row, 1), 2)) / 60

To simplify time-based comparisons, we can use the TIME("hh:mm AM/PM",1) function, which converts times into a 24-hour format as a numerical value.

EXAMPLE: TIME("1:30 PM",1) → 13.50

https://app.smartsheet.com/b/publish?EQBCT=b1bf5a1e8674455eb4134844ee0baab7

jmyzk_cloudsmart_jp

Hi @AngB789

I hope I understand your needs correctly. It sounds like you want to match the Start and End times in Sheet A with the corresponding time slots in Sheet B and pull multiple Slot Numbers into a single cell or a multi-select dropdown format.

To make this comparison more manageable, I added helper columns [S] and [E] to convert the Start Time and End Time into numerical values. This allows for easier filtering of the relevant slots.

https://app.smartsheet.com/b/publish?EQBCT=9c5c9e6b158b4ee9b9a950d516a5c642

[Slot Number] =JOIN(COLLECT({Sheet B: Time Slots : Slot Number}, {Sheet B: Time Slots : S}, >=S@row, {Sheet B: Time Slots : E}, <=E@row), CHAR(10))

[S] =VALUE(LEFT(TIME([Start Time]@row, 1), 2)) + VALUE(RIGHT(TIME([Start Time]@row, 1), 2)) / 60
[E] =VALUE(LEFT(TIME([End Time]@row, 1), 2)) + VALUE(RIGHT(TIME([End Time]@row, 1), 2)) / 60

To simplify time-based comparisons, we can use the TIME("hh:mm AM/PM",1) function, which converts times into a 24-hour format as a numerical value.

EXAMPLE: TIME("1:30 PM",1) → 13.50

https://app.smartsheet.com/b/publish?EQBCT=b1bf5a1e8674455eb4134844ee0baab7

AngB789

@jmyzk_cloudsmart_jp that was very helpful!

jmyzk_cloudsmart_jp

Happy to help!😁