Scheduling

[Adam R via OfficeKB.com]

I am trying to create a spreadsheet to calculate the hours employees work
whilte taking into consideration a .5 or 1 hour lunch.


I would like to type in a start time and an end time (ie 9:00 to 5:00).
These values can be in seperate cells. I would then like to type in a
seperate cell 1 for a 1 hour lunch. And have the total hours for the work
day equal 7.

Then I would like to be able to have a cell for total hours worked, based off
the daily hours worked cells.

My question is very similiar to a question on this link (
http://www.officekb.com/Uwe/Forum.aspx/ms-excel/12878/Scheduling-Frustration )
but no answer was posted.

Any help would be appreciated.

 

[Guest]

This is one way:

Column A has your start time, Column B your lunch break time, Column C your
end time

formula in Column D

=C1-A1-B1

take care with your lunch break. You mesy enter in minutes and hours i.e
01:00 or 00:30

To calculate the sum I used three different cells. This is based on 11
entries so uses D111

Cell 1 (F12)Calulates the Hours worked

=SUMPRODUCT(HOUR(D111))

Cell 2 (F13) Calculates the Minutes

=SUMPRODUCT(MINUTE(D111))

Cell 3 (F15) Calculates the total time

=SUM(F12)+F13/60

I'm sure there will be a super dooper all in one formula which can do the
same thing but hey, it works!

Post back if you have any problems

Thanks,
Matt