booking tool - all day time slots

[EliseFreedman]

Hi All

I am trying to create a booking tool in excel for booking hot desks

I have got a grid with time slots down the left hand side and dates along the top row

There is then another grid underneath where the people book the hot desk by selecting the date, time slot, name, desk number etc.

I am using SumProduct to colour the booked slots in the calender. This is working fine

The one thing I would like to do but not sure how to is to book out a desk for an entire day in one go. I have added a column for All day so if its for the full day then the column will show 1 but it will be blank if its not for the full day.

How do I amend the formula so that if the booking is for the entire day, the entire column will be coloured red

 

[SkipVought]

Hi,
Make all bookings to have a From and To where...
Book >= From And Book < To
=SUMPRODUCT(--(BookTime>=From)*(BookTime<To)*(BookDate=Date))

BTW, you might consider making both TIME and DATE as From To selections, including multiple hour and/or day selections.

And please post your formula.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

So I'm traveling cross country and thot I'd take a crack at a simple booking app.

No colors, just numbers to be able to check OVERBOOKINGS.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Since this is a VBA forum, and not just forum68, I would guess you want to have a VBA solution to your issue. When I saw: 'booking tool', I've thought to myself:

Excel's Form (UserForm) with VBA code to:
Type your name (text box)
Select a Date when you need your 'hot desk' (calendar?), either
- entire day (option)(if any desk is available for that whole day) or
- select time slot(s) (option) available for that day: From / To time
Select desk number (drop-down list?) that fulfills all above requirements

and your worksheet works pretty much like a simple data base / grid display of what's available and when, and what is not.
This way you cannot overbook, make impossible selection(s) and (hopefully) make intuitive application.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson