I am trying to set up a timesheet on excel. I would like to add the hours
worked between a start and completion time, deduct the value taken for luch
(shown as time out and a time back in) from that total and then show any
hours above a 7 hour shift i.e overtime.

standard time

=end_time-start_time-(time_back_in-time_out)-overtime

overtime

=MAX(0,end_time-start_time-(time_back_in-time_out)-TIME(7,0,0))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave Phillips" <Dave wrote in message
...
I am trying to set up a timesheet on excel. I would like to add the hours
worked between a start and completion time, deduct the value taken for
luch
(shown as time out and a time back in) from that total and then show any
hours above a 7 hour shift i.e overtime.

Chip Pearson's site has some good stuff on timesheets including lunch breaks and
overtime.

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

Also a downloadable sample workbook.


Gord Dibben MS Excel MVP

On Sun, 1 Jun 2008 16:15:00 -0700, Dave Phillips <Dave
wrote:

I am trying to set up a timesheet on excel. I would like to add the hours
worked between a start and completion time, deduct the value taken for luch
(shown as time out and a time back in) from that total and then show any
hours above a 7 hour shift i.e overtime.

Chip's got some good info there, but I'd like to go a step further on my time
summary. Rather than require an "IN" entry coming back from lunch I'd like to
have a formula to calculate it and populate that cell with the results. EX:
I take my lunch at 12:30 and enter the time. When I came back I have to enter
another time (13:30) in the next cell. Whether I take 10 minutes for lunch or
3 hours, I'm supposed to enter 1 hour. Duh but it's not my call.

"Gord Dibben" wrote:

Chip Pearson's site has some good stuff on timesheets including lunch breaks and
overtime.

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

Also a downloadable sample workbook.


Gord Dibben MS Excel MVP

On Sun, 1 Jun 2008 16:15:00 -0700, Dave Phillips <Dave
wrote:

I am trying to set up a timesheet on excel. I would like to add the hours
worked between a start and completion time, deduct the value taken for luch
(shown as time out and a time back in) from that total and then show any
hours above a 7 hour shift i.e overtime.

Assume you enter your lunch time in a1, and you want your return time:
=a1+time(1,0,0)

Regards,
Fred

"petz" wrote in message
...
Chip's got some good info there, but I'd like to go a step further on my
time
summary. Rather than require an "IN" entry coming back from lunch I'd like
to
have a formula to calculate it and populate that cell with the results.
EX:
I take my lunch at 12:30 and enter the time. When I came back I have to
enter
another time (13:30) in the next cell. Whether I take 10 minutes for lunch
or
3 hours, I'm supposed to enter 1 hour. Duh but it's not my call.

"Gord Dibben" wrote:

Chip Pearson's site has some good stuff on timesheets including lunch
breaks and
overtime.

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

Also a downloadable sample workbook.


Gord Dibben MS Excel MVP

On Sun, 1 Jun 2008 16:15:00 -0700, Dave Phillips <Dave
wrote:

I am trying to set up a timesheet on excel. I would like to add the
hours
worked between a start and completion time, deduct the value taken for
luch
(shown as time out and a time back in) from that total and then show any
hours above a 7 hour shift i.e overtime.