Employee Time Sheets?
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(I540,40,I5)". This will
give you a total of regular hours up to 40.
Under O/T hours you need another IF statement "=IF(I540,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
" 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.
|