Schedule and Timesheet

[Guest]

We are in between scheduling software and need to use Excel during the down
time for our scheduling
I would like to come up with a formula that will do the following (if
possible) - schedule breaks and lunches with set guidelines
I.E 2 hours after start, etc
Show me where I have staffing (exlcuding when someone is on a break/lunch)
Calculate hours work for the day/week

Name Start Break Lunch Break End total worked
John 09:00 11:00 13:00 15:00 17:30 8.00
Jane 07:00 09:00 11:00 13:00 04:30 8.00

I know there are a lot of knowledgable folks our there, so I'm hoping
someone can lend a hand!

Thanks in advace

 

[Guest]

Let's say your start times are in column A.

B2: =A2+"2:0:0"
or
=A2+2/24
C2 = A2 + "4:0:0" or =A2+4/24
D2 = A2 + "6:0:0" or =A2 + 6/24

etc.

Without knowing the assumptions for "working time", I'm not sure how to
address the next part. Are you saying you have staffing when someone is not

 

[Guest]

Thanks for the response.
To answer your question regarding staffing, we always have to have someone
available to our customers. We usually stagger breaks and lunches throughout
the day so the phones are never left unanswered

 

[Guest]

That didn't really answer my question. How about I make some assumptions
and you tell me if they are wrong.

1 - Your employees are paid for their break time
2 - Your employees are unavailable to customers during their break and lunch
times
3 - Their break time is 15 minutes long
4 - Their lunch time is 30 minutes long

I'm guessing you want to know staffing availability in 15 minute increments.

 

[Guest]

Pretty close!

We do staff in 15 min increments.
We do not pay for lunch
We do pay for breaks

We would like to see staffing availability to indicate when someone is on
break and lunch and not count them as available

For the timesheet portion, we need to pay for their breaks, but not for
their lunch

Sorry for the confusion