Employee Time Sheets?

[mdginzo]

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.

 

[Elkar]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Nick Hodge]

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
(e-mail address removed)

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
(e-mail address removed)

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.

 

[mdginzo]

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

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
(e-mail address removed)

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
(e-mail address removed)

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.

 

[shaunap]

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