Hi all, sorry if this has already been answered elsewhere.
I have 4 columns of timetable data: room number, weekday, start time, end time. I have a separate table with weekday across the top row, hour slot (09:00, 10:00, 11:00...) on the next row and the list of room numbers in a column down the left hand side. I need to write a formula for the table area that will interrogate the source data and return '1' if a room is in use during an hour slot and '0' if it isn't.
I have cobbled together an INDEX + MATCH formula that works as long as the 'room use time' begins on the hour, but it falls down when half-hours are involved (as the match isn't exact). I suspect that I need to nest an IF statement that checks whether the room use time falls between time A + time B, but I'm stumped if I can write it correctly.
Many thanks for any help, and let me know if more info is required.
I have 4 columns of timetable data: room number, weekday, start time, end time. I have a separate table with weekday across the top row, hour slot (09:00, 10:00, 11:00...) on the next row and the list of room numbers in a column down the left hand side. I need to write a formula for the table area that will interrogate the source data and return '1' if a room is in use during an hour slot and '0' if it isn't.
I have cobbled together an INDEX + MATCH formula that works as long as the 'room use time' begins on the hour, but it falls down when half-hours are involved (as the match isn't exact). I suspect that I need to nest an IF statement that checks whether the room use time falls between time A + time B, but I'm stumped if I can write it correctly.
Many thanks for any help, and let me know if more info is required.