making an employee scheduler

I'm making a employee scheduler based on the one provided in numbers under templates under Business > Employee Schedule. I've made a number of changes such as greatly expanding the pop up menu of hours for start and end times for shifts. I needed 15 minute increments instead of half hour increments and needed start and stop times throughout the day instead of just the regular business hours in the original. I also added two columns for each employee to accomodate split shifts. Working pretty good, but with a couple catches.

any shift that starts or ends with 6am won't work. It totals with an insane amount of hours. Perhaps this is because the I'm using the same pop up menu for both start and end times?
for any employee where there are not any hours for the week an error code appears that says "Durations can't be compared to other data types". No idea what that means.

Finally, how does one make available the numbers document for you to review? Now that iwork and mobileme are by the way. I don't see how to attach a document to this message.

MacBook Pro, Mac OS X (10.6.6)

Posted on Aug 21, 2012 1:33 PM

19 replies

Brian Entz Author

Level 1

10 points

Aug 22, 2012 10:18 AM in response to Jerrold Green1

here's the link to the document I'm working on uploaded to drop box

https://www.dropbox.com/s/bw0x6d2vn3k6c9j/MNRH%20EMP%20Scheduler.numbers

Brian Entz Author

Level 1

10 points

Aug 22, 2012 10:40 AM in response to Jerrold Green1

So, are we thinking then that, for example, D6 and E6 be totaled separately and when it exceeds 8 the exceeding portion go to a separate cell to be multiplied by 150%. Actually, when there is a split shift (as we do occasionally) the employee gets an additional hour pay for the bother. That could be a third column inserted between D6 and E6. That new column could be a pop up of 0 and 1 that I would manually trip when setting up a split shift. Then we would actually need total D6,E6,F6.

That would be easy enough, but how do I set up the formula to send what exceeds 8 to a separate cell? I think I can get it from there.

Aug 22, 2012 10:52 AM in response to Brian Entz

Brian,

Here's a version that applies the overtime only to daily hours:

The download is here.

Sunday hours for John is now:

=IFERROR(((TIMEVALUE(C5)-TIMEVALUE(C4)) + IF(TIMEVALUE(C5)< TIMEVALUE(C4), 1, 0))*24 - DUR2HOURS(Administration Information :: $1:$1), 0)

Regular Hours for John is:

=MIN(40,SUM(MIN(8, C6),MIN(8,C9),MIN(8,C12),MIN(8,C15),MIN(8,C18),MIN(8,C21),MIN(8,C24)))

Overtime Hours for John is:

=SUM(MAX(0,C6-8), MAX(0,C9-8), MAX(0,C12-8), MAX(0,C15-8), MAX(0,C18-8), MAX(0,C21-8), MAX(0,C24-8))

Regular Pay for John is:

=C25*C3

Overtime Pay for John is:

=C26*Administration Information :: 2:2*C3

Total Pay for John is:

=C$27+C$28

Regards,

Jerry

Aug 22, 2012 11:03 AM in response to Brian Entz

Brian,

Do you have a rule regarding minimum hours per day? If for instance someone works only an hour or two, would you still deduct an hour for a meal break? How would you write that rule if the possibility exists?

Jerry

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

making an employee scheduler