how do i calculate a work schedule in excel?

[Guest]

I am so close to completing a scheduling worksheet, but have a problem with
one of the formulas. I have created dropdowns for 'in' and 'out', using AM &
PM for the times, a formula to calculate the daily number of hours and a
formula to calculate the total number of hours scheduled for an entire week.
My problem is that I want to deduct lunch periods from the daily schedule,
but have been unable to figure out that calculation. I want to be able to
deduct :30, :45, and :60 minute lunches. At this point, I would even settle
for the ability to deduct a fixed time period of :30 minutes. Any suggestions
are very much welcome!!!

 

[keithl816]

Hi Paul,

If you have the begin time in a1, lunch start in b1, lunch end in c1,
and end of day in d1.

Something like this should work in e1

=((D1-A1+(D1<A1))-(C1-B1+(C1<B1)))*24

Make sure to use this type of time format 15:30 instead of 3:30

Hope this helps

Larry

 

[Gord Dibben]

Paul

Perhaps Chip Pearson's site on timesheets will give you the rest of what you
need.

http://www.cpearson.com/excel/overtime.htm


Gord Dibben Excel MVP

 

[Guest]

Thanks Larry, but I should have mentioned that all our managers want to see
is the start and end times for each day. We have set lunch periods, but not
at specific times during the day. Thanks again though for your quick
response!!!

Paul