I am setting up an employee spread sheet for tracking and billing hours
worked. What I would like to do is enter the total daily hours an employee
works and have Excel seperate and quantify both regular hours and OT hours.
What function can I use to sum daily hours equal to or less than 8 hours, and
sum everything over 8 hours seperately.

Chip Pearson covers this material at his site.

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


Gord Dibben MS Excel MVP

On Tue, 22 Aug 2006 16:03:12 -0700, cmc1111
wrote:

I am setting up an employee spread sheet for tracking and billing hours
worked. What I would like to do is enter the total daily hours an employee
works and have Excel seperate and quantify both regular hours and OT hours.
What function can I use to sum daily hours equal to or less than 8 hours, and
sum everything over 8 hours seperately.

cmc1111, have a look here for timesheet examples, there is also some or
links at the bottom, may even find one you can use :)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"cmc1111" wrote in message
...
I am setting up an employee spread sheet for tracking and billing hours
worked. What I would like to do is enter the total daily hours an
employee
works and have Excel seperate and quantify both regular hours and OT
hours.
What function can I use to sum daily hours equal to or less than 8 hours,
and
sum everything over 8 hours seperately.

If your daily totals are in H2:H8 in time format this formula will give
you overtime

=SUMPRODUCT(--(H2:H8-"8:00"),--(H2:H8"08:00"+0))

formatted as [h]:mm

then for regular hours, if the above formula is in H10

=SUM(H2:H8)-H10


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574426

Would have helped if I had put in the link :(

It was the same as Gord's

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Paul B" wrote in message
...
cmc1111, have a look here for timesheet examples, there is also some or
links at the bottom, may even find one you can use :)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"cmc1111" wrote in message
...
I am setting up an employee spread sheet for tracking and billing hours
worked. What I would like to do is enter the total daily hours an
employee
works and have Excel seperate and quantify both regular hours and OT
hours.
What function can I use to sum daily hours equal to or less than 8
hours,
and
sum everything over 8 hours seperately.

Happens all too frequently from this quarter<g


Gord

On Tue, 22 Aug 2006 19:53:10 -0400, "Paul B"
wrote:

Would have helped if I had put in the link :(

It was the same as Gord's

Gord Dibben MS Excel MVP

Thank you for your help.

If I would like to enter in daily hours worked over a five day work week and
have Excel seperate and sum the Regular hours and OT hours, how can I do that?

"cmc1111" wrote:

I am setting up an employee spread sheet for tracking and billing hours
worked. What I would like to do is enter the total daily hours an employee
works and have Excel seperate and quantify both regular hours and OT hours.
What function can I use to sum daily hours equal to or less than 8 hours, and
sum everything over 8 hours seperately.