Employee Scheduling Worksheet

  • Thread starter Thread starter karyn
  • Start date Start date
K

karyn

I have been using an excel workbook to create my employees schedules. I have
8 worksheets in the workbook; one for each day of the week and the last, a
roll-up of all the schedules. In the individual daily worksheets I have the
columns labels am through 11:30 PM and the rows as the employee names. I
manually enter X's through the times they are scheduled to work. The roll-up
sheets has the columns as the day of the week and the row as the employee
name with the cells as the time frame they are working (i.e. 8:00-1630) I was
wondering if there is a formula or a macro that will return the times
automatically on the roll-up sheet. I can send the attachment I have been
using.
Thanks
 
Here's an idea to get you started.

...........A..........B.........C..........D..........E
1..................800......830.......900.......930
2......Joe....................X...........X..........X
3......Sue........X.........X...........X.............
4......Tia..................................X...........X

In your real file B1:E1 would be true Excel time values.

...........A..........B..........C
9.................Start.......End
10.....Sue........................
11.....Joe........................
12.....Tia........................

Enter this formula in B10:

=INDEX(B$1:E$1,MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0))

Enter this formula in C10:

=INDEX(B$1:E$1,MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0)))

Select both B10 and C10 and copy down to B12:C12

...........A..........B..........C
9.................Start.......End
10.....Sue.....800.......900
11.....Joe.....830........930
12.....Tia......900.......930

If an employee did not work that day the formulas will return #N/A errors. I
would just let that happen then use conditional formatting to hide them.
 
This only works if all the information is on the same worksheet, how do i get
this formula when the info is on another worksheet?
 
Show us a sample of the ouput area you would like, I'm not clear what you
want? I cell summing the total time of an employee on a given day or? It
might also help if you showed us a sample of your data layout on each tab.

For example, suppose your data sheets are in 1/2 increments running from
B1:Z1 with John's data on row 2 in all sheets. Then the following formula
would give John's total hours:

=COUNTA(Mon:Sun!B2:Z2)*2
 
First let me thank you all of all your help....Ok I figured out the formaula
to return the times, just as Biff stated. So my roll-up worksheet looks like
such.

A B C
My question is, is there any way to return a value of OFF rather then the
error #N/A when they aren't sceduled to work?
 
When a person if off is their name not listed on the daily sheets or, their
name is listed but there are no Xs associated with their name?
 
No here is what the rollup sheet look like now

A B C
1 Name Monday Start Monday End
2 Sue 800 1600
3 Joe #N/A #N/A
4 Tia 1100 1700

How can I return a value of OFF rather then #N/A?

Thanks
 
Ok, you misunderstood what I was asking...

The formulas will return #N/A for 1 of 2 reasons:

1. either Joe's name can't be found on the Monday sheet or,

2. Joe's name is on the Monday sheet but there are no Xs associated with
that name.

So, I want to write the error trap based on one of those conditions rather
than trapping the entire formula. This is why I suggested just letting the
#N/A errors happen then hiding them in my original reply.
 
What version of Excel are you using?

If you're using Excel 2007 the error trap is much easier!
 
Thank you for your help but is there a way to show this error as the employee
being OFF? Maybe populate another sheet and do a find and replace type of
formula?
 
Formula for column B:

=IF(ISNA(MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0)),"Off",INDEX(B$1:E$1,MATCH("x",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0),0)))

Formula for column C:

=IF(ISNA(MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0))),"Off",INDEX(B$1:E$1,MATCH("xx",INDEX(B$2:E$4,MATCH(A10,A$2:A$4,0),0))))
 
Back
Top