A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

CONVERT TOTAL HOURS TO REGULAR AND OVERTIME



 
 
Thread Tools Display Modes
  #1  
Old October 10th 06, 06:51 PM posted to microsoft.public.excel.misc
Mark
external usenet poster
 
Posts: 989
Default CONVERT TOTAL HOURS TO REGULAR AND OVERTIME

I am trying to create a spreadsheet that converts the total hours worked in 1
column into two sepate columns that show the total regular and total overtime

NAME TOTAL REG OT
employee 1 10 8 2
employee 2 06 6 0
employee 3 12.5 8 4.5
Ads
  #2  
Old October 10th 06, 06:59 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default CONVERT TOTAL HOURS TO REGULAR AND OVERTIME

Are all the Regular hours 8 or less?

In the Regular column (column C??):
=min(8,b2)

In the OT column:
=b2-c2

where b2 holds the Total hours.

Mark wrote:
>
> I am trying to create a spreadsheet that converts the total hours worked in 1
> column into two sepate columns that show the total regular and total overtime
>
> NAME TOTAL REG OT
> employee 1 10 8 2
> employee 2 06 6 0
> employee 3 12.5 8 4.5


--

Dave Peterson
  #3  
Old October 10th 06, 07:00 PM posted to microsoft.public.excel.misc
Niek Otten
external usenet poster
 
Posts: 3,440
Default CONVERT TOTAL HOURS TO REGULAR AND OVERTIME

http://www.cpearson.com/excel/overtime.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mark" > wrote in message ...
|I am trying to create a spreadsheet that converts the total hours worked in 1
| column into two sepate columns that show the total regular and total overtime
|
| NAME TOTAL REG OT
| employee 1 10 8 2
| employee 2 06 6 0
| employee 3 12.5 8 4.5


  #4  
Old October 10th 06, 07:00 PM posted to microsoft.public.excel.misc
Roger Govier
external usenet poster
 
Posts: 2,886
Default CONVERT TOTAL HOURS TO REGULAR AND OVERTIME

Hi Mark

in C2
=MIN(8,B2)*(B2>0)
In D2
=B2-C2
Copy down as far as required
--
Regards

Roger Govier


"Mark" > wrote in message
...
>I am trying to create a spreadsheet that converts the total hours
>worked in 1
> column into two sepate columns that show the total regular and total
> overtime
>
> NAME TOTAL REG OT
> employee 1 10 8 2
> employee 2 06 6 0
> employee 3 12.5 8 4.5



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Time Sheets - regular vs overtime Susan Excel Worksheet Functions 2 August 29th 06 08:52 PM
+40 Hours in a Week Overtime Fromula Chelsea Excel Worksheet Functions 0 July 28th 06 06:47 PM
Employee Time Sheets? [email protected] Excel Discussion (Misc queries) 5 June 22nd 06 10:11 PM
How to calculate overtime hours in Excel using formulas? Toeknee Excel Discussion (Misc queries) 2 March 1st 06 06:54 PM
need help w/formula for calculating overtime hours jv749297 Excel Worksheet Functions 1 January 17th 05 07:54 PM


All times are GMT +1. The time now is 02:57 AM.


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