Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,510
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
regular and overtime hours Curtis Excel Worksheet Functions 1 April 21st 07 06:32 AM
CONVERT TOTAL HOURS TO REGULAR AND OVERTIME Mark Excel Discussion (Misc queries) 3 October 10th 06 07:00 PM
Time Sheets - regular vs overtime Susan Excel Worksheet Functions 2 August 29th 06 08:52 PM
Show timesheet time in and out in regular time versus military tim John Excel Worksheet Functions 1 November 11th 05 06:14 AM
Calculating Overtime from Hours total Dreamweavn via OfficeKB.com Excel Worksheet Functions 6 April 29th 05 11:21 PM


All times are GMT +1. The time now is 08:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"