excel for daily scheduling needs?

[nightworkergirl]

Hi,

I am a novice to excel and have very limited knowledge of the way that
i could use this spreadsheet to my advantage.
I have a dail schedule that is as follows: i.e.:

Wed 7/5

NAME IN OUT
Alicia V. 7:30 16:00
Amanda S 9:30 18:00
Amber K. 6:30 15:00
Andrea N 8:00 16:30
Carrie P. 7:00 11:00
Cherie D. 10:00 18:30
Christi B 9:00 16:30
Courtney S 7:30 16:00


What I have to do is figure out who works more than 6 hours and less
than 8, they get one break and one lunch, also, the ones who work 8+
get two breaks and one lunch, the breaks must be evenly distributed,
keeping at least four people on break or lunch at one time, optimally.
i am so sick of wastin hours of time to get this done in between phone
calls and i know there must be one or more formulas i can use to get
this accomplished.
Please someone help!
Nicole

 

[Bob Phillips]

Nicole,

It is simple to get the number of breaks a person is due with

=1+((C2-B2)>TIME(8,0,0))

But a bit more info on who and when. Is the lunch break a set time, or does
the 4 rule apply for lunch? And what sort of time does breaks start (how
long are they)? It seems silly for instance that someone would come in at
7:30 and have a break at 8:00.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[nightworkergirl]

the breaks would have to be two hours apart, varying with say a five
hour shift where the break would preferably be 2.5 hours, but for all
practical purposes, two hours would be fine.

 

[nightworkergirl]

ok, the breaks would be 15 min and the lunch would be thirty. We are
aiming for a two hour lapse between times, but in some instances,
(where someone would five hours for instance) it would not have to be
exact.

Also, how would i format the times so that it would accurately
calculate the time that they are here, not just adding the times
together?

Thanks a bunch Bob!

Nicole

 

[Bob Phillips]

Nicole,

Here is a (rather simplistic) example.

What I have done is to calculate the lunch times and then calculate the
first break halfway between the start and lunch-break. It does mean that
breaks can be over-schedule, but I have highlighted those, and I suggest
that is where you do manual adjustment. Similarly, if someone has a second
break due, I calculate that as halfway between the lunch and end time.You
could make it all automatic by formulae and code, but that would be quite
complex and I don't have time to do that for you.

I have created it so that the calculations are don off to the right, and the
actual schedule can be over-typed.

http://cjoint.com/?hflTdZRrft

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[nightworkergirl]

ok, so what do i have to do, just type in the in and out time for the
sheet to calculate it?
I am sorry, i did express that i am a novice when it comes to excel...!


thank you in advance for your time.

Nicole

 

[Bob Phillips]

Gosh, I thought you had given up <g>

Yes, just type the in and out times for all the people in columns B and C
and the worksheet works itself out. As I said I haven't automatically
avoided overscheduling, but I do highlight them, so I suggest that you then
manually adjust those in columns E-F. Just remember to restore them when you
want to calculate the next day, or use the spreadsheet as a template.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)