Employee Time Sheets?

  • Thread starter Thread starter mdginzo
  • Start date Start date
M

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.
 
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
 
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)
 
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)


Nick Hodge said:
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.
Click to expand...
Click to expand...
 
Sorry, Ican't get this to work. Anybody wanna email me a spreadsheet
withthis already done somehow? I apologize.

Nick said:
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)


Nick Hodge said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top