Schedule Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to design a spreadsheet that has the following information.
Seat number, person's name, in time, out time. I would then like excel to
formulate and show the times on one page in which a particular seat number is
not occupied. I have not figured out the most efficent way to design this.
Any ideas would be appreciated.
 
Thank you so much. This is exactly what I need. I do have a question
though. I need to add about 50 Names. If I go and change the ranges to 50.
IE A2:A7 changed to A2:A50 the sheet stops working. I also need to add the
times from 12:00 am to 11:30 pm. If I try to add a time block say 11:00 pm
to 11:30 pm and I copy the formula across and down and then change one of the
current people to 11:30 pm as the to time it does not reflect occupancy in
the new hour. This is perfect for what I need. If you can tell me if there
are any secrets to manipulating the formulas to what I need then I will be on
my way. Thank you so much for taking the time to put this together.
 
Thank you so much. This is exactly what I need.

welcome, good to hear that.

Here's a revised template customized to suit your specs (full details inside):
http://www.flypicture.com/download/NjYyMA==
Seats_Occupation_Schedule_2.xls
(Template is set-up assuming 5 seats)

Notes in sample:
-----------------
Note: Select/Enter the OutTimes (in X's col D) with an extra minute, eg if
OutTime is 1:30 AM, enter it as 1:31 AM (InTimes can be entered as normal)

In X,

Data for Seat#, Name, InTime, OutTime
are entered/selected within A4:D51

Seats Occupation/Booked Status
Array-enter** in H4
=IF(ISNA(MATCH(1,($A$4:$A$51=H$3)*($C$4:$C$51<=$F4)*($D$4:$D$51>$G4),0)),"",INDEX($B$4:$B$51,MATCH(1,($A$4:$A$51=H$3)*($C$4:$C$51<=$F4)*($D$4:$D$51>$G4),0)))
Copy H4 across / down to L51 to populate

**Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Select H4:L51 and apply conditional format using formula is: =H4=""

Seats UnOccupied Status
In N4:
=IF(H4="",TEXT($F4,"h:mm AM/PM")&"-"&TEXT($G4,"h:mm AM/PM"),"")
Copy N4 across / down to R51 to populate

Select N4:R51 and apply conditional format using formula is: =N4<>""
 
I don't want you to get the wrong idea. I really appreciate what you have
done, I just can't seem to modify this. I really like what you have done. I
guess I will ask if this is possible, but please don't spend a bunch of time
on this. I need to do two more things with this. I need 36 seats. I would
also some how like to add the days of the week to this. (if this is too
complicated don't do it we can do an individual spreadsheet for each day of
the week). The purpose is to enter employee schedules, show the seat as
occupied on the days that they are here, and then I want to identify 9 hour
blocks of open seats on each day so that someone could sit in that desk for
their 8 hour shift. This is almost there! Thanks.

Brent
 
Brent,

The earlier link is good (just tested it again here), but I think you can't
just click on the link from where you're reading this reply.

Try copy n paste the *entire* string below (the string is inclusive of the
"==" at the end) into the address bar in your browser:

http://www.flypicture.com/download/Njg5Mg==

then just press ENTER

Alternatively, here's another d/l link to the template:
http://cjoint.com/?hDiEC1N3hz

I'm having some problems uploading files to savefile.com recently ..
 
Max,

I have began entering the data. Working great! Is there a way to expand
the number of employees on the left one more time. I am thinking if we could
double it this would be good for all applications. Everything else stays the
same, just 100 employee possibilities. I didn't calculate for split shifts
or carry overs from the previous day. Thanks,

Brent
 
Here you go, with input extended to cover up to 100 staff:
http://www.flypicture.com/download/ODM4OQ==
Workstation_Scheduling_24_hr_x_36_seats_x_100_staff.xls

(remember to copy n paste the *entire* link above into the browser address
bar)

The array-entered formula in the top left cell H4 is changed to cover the
extended range:
=IF(ISNA(MATCH(1,($A$4:$A$103=H$3)*($C$4:$C$103<=$F4)*($D$4:$D$103>$G4),0)),"",INDEX($B$4:$B$103,MATCH(1,($A$4:$A$103=H$3)*($C$4:$C$103<=$F4)*($D$4:$D$103>$G4),0)))
H4 is then copied across/down to populate.
 
Max,

Thanks for the update. I copied the entire link into my browser bar but
flypicture gives a file not found error. Can we use a different site again?

Thanks,

Brent
 
Back
Top