Help with Weekly Personel Schedule

[John D. Watker]

Hey everyone,

I've been using Excel to create the weekly schedule for my department at
work (roughly 8-10 people), as well as daily assignment sheets that list the
people on, hours working, and an hour-by-hour breakdown of their job
responsibilities. Because the hours available to my department, as well as
the tasks required for completion, vary week to week, I create my schedule
from the daily assignment sheets, and then transfer my work over to a weekly
schedule grid (consisting of simply the name, date, and time of their shift)
for posting in our back office.

Example

1/1 1/2 1/3 1/4 1/5
1/6 1/7 TOTAL
John 9-5 8:30-4 10-6 off off 9-5
9-5 39.5 hours
Bob off off 11-4 11-4 9-5 off
off 18 hours


I have two questions:

1) Can I automatically import someone's time from the daily assignment
worksheet and have it go into the correct cell for that persons name and
day.

2) Can I have excel then add those shifts up into the total hours that
person is working for the week? Could I also have excel subtract lunch
breaks from that time automatically?

Thanks for any help,

John

 

[David McRitchie]

Hi John,
It is hard to read what you have as it does not line up.
You have an input and an output sheet, you show only one.
If you want Excel to calculate times you need to place the
start and stop times in separate columns.

If you have a sheet that shows each half hour for a week you
can sum the number of 1's using SUMIF Worksheet Function
http://www.mvps.org/dmcritchie/excel/sumif.htm

If you count 72 one's (or x's) you can divide by 2 to get hours
if you want the hours in an Excel time format then divide again by 24 so
that it becomes a fraction of a day which is what Excel times are.
http://www.mvps.org/dmcritchie/excel/datetime.htm

You can reference another sheet directly if you know the location
of the cell you are looking for, or if the names are jumbled by VLOOKUP.
You need a clear description of your data in order to solve your
problem, and even more important if you want help. The sheet
references may or may not be of help, I've just chosen some sheets
that match some of the words -- use them to help solve a problem
that you can describe, don't look to the sheets for answers if you
can't describe what you want.
http://www.mvps.org/dmcritchie/excel/sheets.htm
http://www.mvps.org/dmcritchie/excel/vlookup.htm

You might try a Google web search on
excel hourly scheduling
and the same search in a Google Groups search.