How to seperate OT and Reg. Hours from total hours

[cmc1111]

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.

[Gord Dibben]

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 <[email protected]>
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.

[Paul B]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[daddylonglegs]

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

[Paul B]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

[Gord Dibben]

Happens all too frequently from this quarter<g>


Gord

On Tue, 22 Aug 2006 19:53:10 -0400, "Paul B" <[email protected]>
wrote:

>Would have helped if I had put in the link
>
>It was the same as Gord's

Gord Dibben MS Excel MVP