Schedule Worksheet help please

[Jim]

Is there a way to input times on sheet 1 and have it automatically
fill-in the appropriate time slots on sheet 2.

For example:
Jim works Monday from 5:30 am to 1:30 pm.
Tim works Monday from 1:00 pm to 9:30 pm.

If I had the format on sheet 2 set-up in half hour increments is
there a way to have it read what is input on sheet 1 and fill in the
blanks on sheet 2.



How are the times input on Sheet1? Is the information split into
columns, e.g.:
Column a: names
Column b to h: days of week
name sun mon tues wed
James OFF 5:00a - 1:30p 5:00a - 1:30p 5:00a - 1:30p
etc...

Does Sheet 2 only show the Monday shifts, with other days on different
sheets?
Yes, each day is represented by it's own sheet.

What's the earliest start time for a shift, and the latest finish
time? Do any shifts start on one day and finish the next day?
Times go from midnight to midnight, and we run 24 hours a day and some
shifts will overlap from one day to the next.

How do you want the time slots filled in -- with a name, an "x", some
shading?
I would prefer the slots to be filled in with the number 1 so that we
can count at the bottom of the sheet how many people are scheduled for
a particular hour with shading.

Thank you,

Jim

 

[StarTrek an beyond]

Hi Jim,
To be clear, questions

1. Is all the information being filled in on sheet 1 ONLY?
if yes, you could use either VLOOKUP OR HLOOKUP.

go on down for the other questions

Is there a way to input times on sheet 1 and have it automatically
fill-in the appropriate time slots on sheet 2.

For example:
Jim works Monday from 5:30 am to 1:30 pm.
Tim works Monday from 1:00 pm to 9:30 pm.

If I had the format on sheet 2 set-up in half hour increments is
there a way to have it read what is input on sheet 1 and fill in the
blanks on sheet 2.

Why are you using 1/2 hour increments? Are you finding the total time?
1:30 - 5:30 (13:30-17:30) in the spread sheet. Send an email to me
(e-mail address removed) and I will send a book that I use for my time sheet
from a job I am doing right now. I have two sheet, one linked to the main
time sheet. May be this can help.
Wayne B

 

[Debra Dalgleish]

Jim,

One possible solution:
1. Enter the start and end times in separate columns, e.g.
name sun mon tues wed
Start End Start End Start End Start End
James 5:00 13:30 5:00 13:30 5:00 13:30
2. Create two rows of data for employees who have two shifts in one day
For example, an employee working the 11:00 PM to 7:00 AM shift --
Mary 0:00 7:00 0:00 7:00
Mary 23:00 23:59 23:00 23:59 23:00 23:59
3. On the daily sheets, use the sumproduct formula to display a one or
a zero for each employee. For example, with time from B1 to AX1, and
employee names in column A, enter the following formula in cell B2,
and copy down and across:

=SUMPRODUCT((List!$A$3:$A$6=$A2)*(List!$H$3:$H$6<=B$1)*(List!$I$3:$I$6>=B$1))

where List is the sheet which contains all the start and end times.

Debra