Flagging a double booked room

jg124

Hi Guys

I have a tracker that my team use to manage their daily tasks. However, due to COVID there can only be a specified number of people in a room at a certain time. Often rooms have been double booked, resulting in work being delayed.

I want to highlight whether a room has been double booked on a specified date by an OTHER team, i.e. Team 1 can have multiple bookings of that room for a day (1 room might have 3-4 events against it for a day), but if Team 2 wishes to book that same room, raise a flag.

My formula is 2/3rds of the way there, just struggling on the team bit:

=IF(COUNTIFS(Room:Room, =Room@row, [Start Date]:[Start Date], =[Start Date]@row, Team:Team, <>Team@row) > 1, 1)

Is there any other way I could approach this? Thanks!

Heather Duff

Hi! I was able to get it working by using this formula (which is nearly identical to yours):

=IF(COUNTIFS(Room:Room, Room@row, [start Date]:[start Date], Date@row, Team:Team, <>Team@row) > 1, 1, 0)

Let me know if it works!

Best,

Heather

Heather Duff

Hi! I was able to get it working by using this formula (which is nearly identical to yours):

=IF(COUNTIFS(Room:Room, Room@row, [start Date]:[start Date], Date@row, Team:Team, <>Team@row) > 1, 1, 0)

Let me know if it works!

Best,

Heather

Paul Newcome

How are people booking the rooms? Are they using a form or entering directly into the sheet?

jg124

@Heather D thank you so much, I was nearly there ! :P

@Paul Newcome we are manually entering it in to a Grid, any suggestions/ improvements from a master such as yourself are always appreciated!

Cathy Betzer

@jg124

Hello, are you using a grid for tracking ? We are building something similar to reserve rooms during covid to ensure social distancing. I'd enjoy see what you built. Thanks