Employee Time Sheets?

Anyone out there can help me with a worksheet project? I am trying to
set up a timesheet formula where I can input the total number of hours
worked in the wek and then have it calculate into two seperate cells,
regular hours (hours under 41) and overtime (hours over 40). I know
this seems simple,but how do I do it if the hours are less than 40?
Does this make sense? This is stumping me right now.

Let's say your Total Hours are in Cell A1.

For your "Regular Hours" use this formula:

=MIN(A1,40)

For you "Overtime Hours" use this formula:

=MAX(A1-40,0)

HTH,
Elkar


"[email protected]" wrote:

> Anyone out there can help me with a worksheet project? I am trying to
> set up a timesheet formula where I can input the total number of hours
> worked in the wek and then have it calculate into two seperate cells,
> regular hours (hours under 41) and overtime (hours over 40). I know
> this seems simple,but how do I do it if the hours are less than 40?
> Does this make sense? This is stumping me right now.
>
>

Sum the total hours (You will need to format this as [hh]:mm or hours over
24 will show as days

Then, presuming this sum is in G1, enter in H1

=IF(G1<1.708333,G1,1.666666)

in I1 enter

=IF(G1>=1.708333,G1-1.708333,0)

format the results as [hh]:mm (via Format>Cells...>Custom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
> Anyone out there can help me with a worksheet project? I am trying to
> set up a timesheet formula where I can input the total number of hours
> worked in the wek and then have it calculate into two seperate cells,
> regular hours (hours under 41) and overtime (hours over 40). I know
> this seems simple,but how do I do it if the hours are less than 40?
> Does this make sense? This is stumping me right now.
>

Adding to my answer, as you have a different interpretation from Elkar...I
am presuming you are entering times as times recognisable to Excel, 40:00:00
as opposed to whole numbers (40), which Excel will see as days

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"Nick Hodge" <[email protected]> wrote in message
news:%[email protected]...
> Sum the total hours (You will need to format this as [hh]:mm or hours over
> 24 will show as days
>
> Then, presuming this sum is in G1, enter in H1
>
> =IF(G1<1.708333,G1,1.666666)
>
> in I1 enter
>
> =IF(G1>=1.708333,G1-1.708333,0)
>
> format the results as [hh]:mm (via Format>Cells...>Custom)
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Anyone out there can help me with a worksheet project? I am trying to
>> set up a timesheet formula where I can input the total number of hours
>> worked in the wek and then have it calculate into two seperate cells,
>> regular hours (hours under 41) and overtime (hours over 40). I know
>> this seems simple,but how do I do it if the hours are less than 40?
>> Does this make sense? This is stumping me right now.
>>
>
>

Sorry, Ican't get this to work. Anybody wanna email me a spreadsheet
withthis already done somehow? I apologize.

Nick Hodge wrote:
> Adding to my answer, as you have a different interpretation from Elkar...I
> am presuming you are entering times as times recognisable to Excel, 40:00:00
> as opposed to whole numbers (40), which Excel will see as days
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> "Nick Hodge" <[email protected]> wrote in message
> news:%[email protected]...
> > Sum the total hours (You will need to format this as [hh]:mm or hours over
> > 24 will show as days
> >
> > Then, presuming this sum is in G1, enter in H1
> >
> > =IF(G1<1.708333,G1,1.666666)
> >
> > in I1 enter
> >
> > =IF(G1>=1.708333,G1-1.708333,0)
> >
> > format the results as [hh]:mm (via Format>Cells...>Custom)
> >
> > --
> > HTH
> > Nick Hodge
> > Microsoft MVP - Excel
> > Southampton, England
> > www.nickhodge.co.uk
> > [email protected]HIS
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> Anyone out there can help me with a worksheet project? I am trying to
> >> set up a timesheet formula where I can input the total number of hours
> >> worked in the wek and then have it calculate into two seperate cells,
> >> regular hours (hours under 41) and overtime (hours over 40). I know
> >> this seems simple,but how do I do it if the hours are less than 40?
> >> Does this make sense? This is stumping me right now.
> >>
> >
> >

OK, Say the following is your data in columns B - H with tot - ot in I - K

Sun Mon Tues Wed Thurs Fri Sat Tot Reg
O/T
5 8 8 8 8 8 5

Under Total put a simple sum formula =sum(B1:H1) This will give you a total
of all hours worked.
Under Regular hours you need an If Statement "=IF(I5>40,40,I5)". This will
give you a total of regular hours up to 40.
Under O/T hours you need another IF statement "=IF(I5>40,I5-40,0)". This
will give you a total of all hours worked over 40 in the week.

If you also need to calculate hours of o/t worked on a per day basis in
addition to the o/t calculated on a weekly basis then you're going to have to
get into nested if statements. By this I mean if overtime is paid on
anything over 8 hours a day and or over 40 hours a week. Somebody could work
20 hours in one week but still be entitled to 4 hours of overtime if they put
in two 10 hour days.

Hopefully this helps you out.

Shauna

"[email protected]" wrote:

> Anyone out there can help me with a worksheet project? I am trying to
> set up a timesheet formula where I can input the total number of hours
> worked in the wek and then have it calculate into two seperate cells,
> regular hours (hours under 41) and overtime (hours over 40). I know
> this seems simple,but how do I do it if the hours are less than 40?
> Does this make sense? This is stumping me right now.
>
>

Hi did you come up right with your excel sheet?