Desk allocation

Seaglass

New Member
Joined
Feb 19, 2019
Messages
1
Hi

I'm sure this is simpler than I'm finding. I have four columns of data which the number of rows vary from week to week. The data set is normally multiple days.

Date | Name | Start | End

The thing...
I want to allocate each person a desk however I want it to check the end time of people already assigned a desk so if the person given desk 2 finishes at 3pm I can reuse that desk for someone starting later than that. All people start after 8am and finish before 8pm so there is no instances of someone working across multiple days; although they will work multiple days.

Any ideas? I'm presuming any formulas using "IF" will be long and I dont know enough about VBA to start writing it from scratch, only enough to read through it and make sense of what's happening.

Thanks
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
Hi Seaglass and welcome to the Forum

I'm not clear about the layout of your sheet and how you will populate it.:confused:


Questions:
  1. Where does the Desk No. appear?

  2. Are Start and End times?
  3. Are desks booked out for minimum time blocks (e.g. 30 min, 1 hour)?
  4. For what period is the data - just one fixed week, a rolling week, or ???

  5. What is it that you're wanting a formula (or VBA) to actually do?
  6. Is Excel the right tool for the application?

Could you rearrange your table of data such that it looks like a basic appointment diary:

Date | Start | End | Desk 1 | Desk 2 | Desk 3 |Desk 4 |Desk 5
22/2 | 8:00 | 8:30 | John D | _____ | _____ | Bill G_ |_____
22/2 | 8:30 | 9:00 | John D | Julia R | _____ | Bill G_ |_____
22/2 | 9:00 | 9:30 | John D | Julia R | _____ | _____ |_____
22/2 | 9:30 | 10:00 | John D | _____ | Clint E | _____ |_____
22/2 | 10:00 | 10:30 | John D | _____ | Clint E | _____ |_____
and so on

If the data set is long you could then use Autofilters to check any desk for vacancies.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,489
Messages
5,529,168
Members
409,853
Latest member
Amy Gassett
Top