Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if then formula for total time ;overtime hours versus regular
Hi I am a business owner and do my own invoicing /payroll I have a spread
sheet setup in excell for this.Currently my employees have there own column where each day I insert there total hours worked so for example if you were to sum up column l45:l51 this is the total hours the individual works in a seven day period. I am trying to create underneath the sum at cell l52 and l53 a formula that will seperate the regular hours l52 and overtime hours l53. Overtime is based on hours worked over 40 in a week or if you start mid week and total is 40hr or less then anything past 8 daily is overtime. So far all I get to work is the sum I know not much any help anyone can give me would be great....thnx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if then formula for total time ;overtime hours versus regular
Hi,
It's a complex issue to give an exact answer because it depends on how you have set up the worksheet. However, I'll give you some pointers on how you might go about calculating your results. Firstly assume 8hrs per day over 5 days totals 40hrs. Therefore overtime hrs can be calculated as total hours less standard hrs. or as a formula:- = Total overtime hrs - (8x5). Assuming a 5 day week Now you can count the number of days that an employee worked by counting the daily hours worked which exceed zero. Therefore you can calculate the normal hours by multiplying this result by 8. the formula to do this is:- =COUNT(A3:E3,"0") assuming that A3:E3 contains the daily total hours. Therefore everyones overtime can be calculated by the following irrespective of how may days they worked for the week:- Overtime = Total hours worked - (COUNT(A3:E3,"0")) X 8 Hope it helps to put you on the right track. Regards, OssieMac "OTVs Regular" wrote: Hi I am a business owner and do my own invoicing /payroll I have a spread sheet setup in excell for this.Currently my employees have there own column where each day I insert there total hours worked so for example if you were to sum up column l45:l51 this is the total hours the individual works in a seven day period. I am trying to create underneath the sum at cell l52 and l53 a formula that will seperate the regular hours l52 and overtime hours l53. Overtime is based on hours worked over 40 in a week or if you start mid week and total is 40hr or less then anything past 8 daily is overtime. So far all I get to work is the sum I know not much any help anyone can give me would be great....thnx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
regular and overtime hours | Excel Worksheet Functions | |||
CONVERT TOTAL HOURS TO REGULAR AND OVERTIME | Excel Discussion (Misc queries) | |||
Time Sheets - regular vs overtime | Excel Worksheet Functions | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
Calculating Overtime from Hours total | Excel Worksheet Functions |