HELP!! Formula for calculating overtime

I'm hoping someone can help me.  I can't seem to figure out a formula to 
calculate OT for my staff.  Regular hours per day are 7.5.  I'm looking to 
create a formula for banking time which will break out in two columns hours 
worked over the regular 7.5 hours per day up to 11 hours per day, and in the 
second column the hours worked over 11 hours per day.

Does anybody have any ideas?

Thanks so much!!
2/17/2010 5:15:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 35

Something like this

Hours      Bank1	Bank2
6	 0	0
7.5	0	0
8.5	1	0
9.5	2	0
10.5	3	0
11.5	3.5	0.5
12.5	3.5	1.5
13.5	3.5	2.5
14.5	3.5	3.5
15.5	3.5	4.5

assuming the 6 is in cell A2 the formulas for b2 and c2 are as follows

b2  =IF(7.5<A2,MIN(A2-7.5,3.5),0)
c2   =IF(A2>11,A2-(7.5+B2),0)

Copy down as needed
If this helps, please remember to click yes.

"Teri" wrote:

> I'm hoping someone can help me.  I can't seem to figure out a formula to 
> calculate OT for my staff.  Regular hours per day are 7.5.  I'm looking to 
> create a formula for banking time which will break out in two columns hours 
> worked over the regular 7.5 hours per day up to 11 hours per day, and in the 
> second column the hours worked over 11 hours per day.
> Does anybody have any ideas?
> Thanks so much!!
2/17/2010 7:17:01 PM

Similar Artilces:

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

In the last three days, I have unable to download attachments. Previously I had no problem. When I attempt to open my attachments, I get a window called File Conversion filled with ASCII characters. Need info on how to correct this. It sounds as if these attachments are defaulting to Microsoft Word to open. What types of files are these? What are the file extensions? -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked -Please post your Outlook version! "Manville" <

How do I display a formula calculation in plot area of a chart?
I want to add a text box in the chart that returns the value from a formula that was entered on the data page. The formula value is not related to any series value. WiseGuyOne: You need to build your text expression in a cell, then insert that expression to the chart. First, to build your text expression: 1. Select your cell 2. Enter = first to let Excel =know you are adding a formula 3. You can now enter text within quotes and cell values .. here's an example =" Yesterdays average temperature was " & sheet1!$a$3 Note that the & is used to add ...

Looking for help...... In column A1:A16, I have names. In B1:B16 I will assign random numbers to those names from 1-16. In another part of the sheet I have 16 columns lets say D:S, (D) being column 1 and (S) being column 16. when I assign a number in column B, I want the name to the left to appear above the appropiate column. EX, If B1 is 16 then the name in A1 will appear above column S. I am using Excel 2007 Windows Vista. I find this site to be the place for answers,everyone is great. Thanks in advance. D1: =INDEX($A$1:$A$16,MATCH(COLUMN()-3,$B$1:$B$16,0)) Fill to S1. "JOHN...

help help remote login
i need to access the exchange server remotly. we have a vpn set up and i don't know how to get in to do simple things like delete the bad mail folder ect.. or access simple files. if some one could guide me the right way or help it would be muc appriciated. thanks tony Have you considered remote desktop? Nue "tmolaee" <> wrote in message >i need to access the exchange server remotly. we have a vpn set up and i > don't know how to get in to do simple things like dele...

Hello! I'm doing a sheet at work and I'm having some problems, I need your help! Whay I have is a sheet with some columns and rows. - 1: Can I put "filters" on SOME columns only? I can put filter with auto-filter to all columns, but it makes no sense... How to put on SOME only? - 2: Can I auto create a book when inserting some data on a cell (write down something on a blank cell)? use the same name as text written on cell? - 3: Complete some cells auto from specific cells on other sheet (file) of excel? - 4: Hide some columns so some users on a Domain don't see......

how to enlarge publisher business card to letter size 8.5x11 A single card? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "notoes" <> wrote in message > how to enlarge publisher business card to letter size 8.5x11 must have a big wallet to keep them in -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "JoAnn Paules" <> wrote i...

Urgent Help Required on Excel Macro Problem
Hi there, We are facing one problem from around 2 months.Some of our users have XF_SIC.A virus found in their pc.XF_SIC.A virus generally found in excel workbook as macro virus.This virus create one excel 4.0 macro sheet with "very hidden and very protected" attributes in excel 97 workbooks which can't be removed as it is not seen.every time when infected excel 97 file opens it infect the default startup excel file(Book1 placed in XLSTART folder) and then every excel file that will be open will have infection as default file book1 our antivirus software mcafe...

Supplemental info to "HELP-excel date malfunction
Here's some additional info to the earlier post on the date cell function malfunction. Been isolated to the function to insert the date (cell format/date). It seems the application cannot interpret the system date properly. Always inserts/interprets the date as CurrentMonth-01-2011, instead of actual date. I cannot use the application this way. I am using the 'general' setting in cell format, then entering the string Month(abr)-day [eg= dec-17] instead of letting application enter the date automatically. Is there a patch available for this issue? -- Visit Family Rad...

Need Formula to Average Monthly Increase
I have a spreadsheet where there are columns for each month of the year Each cell contains a number of loan submissions that graduall increases per month as we gain more business from each mortgage vendor I need a formula that can look at multiple cells in a row (i.e Jan-Dec) per vendor and then give me a result that shows the averag monthly percentage increase factor in submissions... Example: MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A SUBS 1 5 18 29 30 40 57 85 150 275 ......... ..I will be using this data fo forecasting future submissions ...

Going Back in History formula?
How can I express a formula that would back 300 rows and look at every other row for the 3 cells with random single digits from 0 to 9 and calculate, the how many rows back since the oldest 0-9 digit occurred in cell 1 answer, cell 2 answer and 3 answer, then calculate which 0-9 digit has not occurred in cell 1, cell 2 and cell 3 the longest and displays how many row back since the last occurrence. So that would be 4 separate formulas ------------------------------------------------ ~~ Message posted from ~~ View and post usenet messages directly from http://www.Exc...

How do I enter a time formula into a worksheet that determines if.
How do I, set about entering a formula within a worksheet that determines if the Time is greater or less that the time in a certian cell; eg =if(now()>K17,"then display this","") K17 holding the Value 08:30 AM formated to time =IF(MOD(NOW(),1)>K17,"then display this","") HTH Jason Atlanta, GA >-----Original Message----- >How do I, set about entering a formula within a worksheet that determines if >the Time is greater or less that the time in a certian cell; >eg =if(now()>K17,"then display this","") ...

Existing formula
I'm working with a spreadsheet that was built by someone else and cam across a formula in some of the cells that I'm puzzled by. The formul begins with "=", but the next character is "+", then a reference t another worksheet and cell. The "+" is what I'm wondering about, haven't seen it used in this context before, can someone help me ou here? Some examples of what I'm looking at are: =+Inputs!G9 =+H17-R20 Thanks, wwhit -- Message posted from It's an unnecessary character that is there because the person ...

How to access help?
That office assistant thing is useless to me. It says you can switch it off but how? And where do I find stock standard help? Or does that only come up when you've got the paperclip switched off? In which case, again, how do I switch the thing off? How to modify or remove Office Assistant Help in Office 2000 -- Mary Sauer MSFT MVP news:// "abrogard" <> wrote in message news:36DF541B-4845-454E-B610-4BA5DFD28952@mi...

Help: how to print fill pattern well in any printers
As we know, for display, we can call CreatePatternBrush() to create a 8pixel * 8pixel to fill a region. It is perfect for display in the display device and some printers. But In some high-res printer, the fill pattern is printed as gray. (the fill pattern is printed so densy that it looks like a solid gray block, for example, some HP printers and Acrobat PDF printer). I have tried a bigger bitmap brush according to the printer device's resolution. But It is still bad for some printers. Any suggestion is appreciated. Is there any way that is suitable for any printers? ...

formulas changing when emailed
At work, I have created a spreadsheet that has links to workbooks in a user drive..called the "U: drive." When I transfer this excel project to others via email, I make sure that the other workbooks also have the same name in their U: drive as well. However, whenever I email it... I have to change many of the formulas at their computers bc the references in my formulas change to a really long string. I have tried using "CNTL+H" to replace the "=" to a "+" but it doesn't work for me bc I have "+" "=" elsewhere in my formulas....

Help with RecordSetClone
Hello, this is my first attempt at using recordsetclone to look at the same field on a continuous form and update the field. I have looked at other examples on this forum and put together the below code but I keep getting an "invalid argument 3001 error" when it reaches the first .FindNext. With Me!subfrmPersons.Form.RecordsetClone .Bookmark = Me!subfrmPersons.Form.Bookmark .FindNext Me!subfrmPersons.Form![DOB] MsgBox Me!subfrmPersons.Form![DOB] 'for testing only If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form! [Age]...

Little help, 5.5 to 2k3 migration problems (long)
Hi, First post here, so I apologise in advance if I get anything too wrong, or it's too long. I had Exch 5.5 running on a Win2k domain controller, 60 odd users, some public folders, some delegate access etc. I decided to migrate the system to a Win2003 / Exchange 2k3 server, following the deployment instaurations on the CD. *Note: on further investigation on the KB, it seems that I should have run the 5.5 DS/IS constancy checker before the migration to iron out any ACL/AD issues - this wasn't done. I have also discovered that my original ex5.5 site name contained invalid character...

excel help 04-09-10
I have a table (range a5:s33) that is changing constantly and its being updated. I need cell I1 and I2 to give me the date (I1) and time (I2) when any of the cells within the range are modified....any help?...thanks Try the following Sheets Event-Macro: ----------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, [A5:S33]) Is Nothing Then [I1] = Date [I2] = Time End If End Sub --------------- Micky "jcheko" wrote: > I have a table (ran...

Excel 2003 - VBA
Hi Guys: I'm drawing a blank and need some help. I have a table with row 3 containing sheet names (currently pointing at 106 sheets). In rows 4-250 I want to put formulas that will look for something (located in Column B) in each of the sheets named in row 3. The first cell in the target sheet contains the last row with valid data. Example: Cell "D3" = P02-05 Cell "B5" = 123456789IBM In cell "D5" I want to generate a formula that will look in sheet P02-05, search for the contents of "B5" throughout the sheet and return the Value in colum...

Excel data completely disappeared
Please can someone help me with a problem I am having. I created a spreadsheet with Excel and included a 'comments' column designed to appear with a mouseover. I saved the spreadsheet and tested it out, everything ok ... until I went to view it the next day and every single comment has disappeared !! Even the red triangles have completely disappeared ... this is really weird. Can anyone throw any light on this ? TIA, cool4cats -- cool4cats ------------------------------------------------------------------------ cool4cats's Profile:

Help with referencing multiple sheets in formulas
Greetings Xcellers I have a workbook with 32 sheets, The first sheet is a month summary and the other 31 sheets are for each day of the month. The sheets are names month,1,2,3..31 I need to copy information from the daily sheets to create a summary on the month sheet like follows A B 1 Day Turnover ..... 2 1 ='1'!E7 3 2 ='2'!E7 4 3 ... 5 5 ='5'!E7 Is there a way to substitute the sheet name from the column Day instead of manually having to enter each formula. There will be a number of columns and I have a number of similar workbooks to make. IE for B2 a formula l...

Calculating the difference between two times
I need to calculate the difference between two times. I do not have a problem when going from 12:00am to 11:59am. I also figured out how to calculate the differnce between 12:00am to 11:59pm. How do you calculate the difference between two times when it goes over two days. For instance from 10:00am (10/03) to 1:00am (10/04). That is 15 hours, but I cannot get that answer. Also, the dates are not important, I just need to be able to calculate that as 15:00 hours. With the start time in A1 (10:00 AM) and the end time in A2 (01:00 AM) use =MOD(A2-A1,1) Regards, Peo Sjoblom &...

Need help with excel sorting/comparing funcations
HI all, the list I am working on consist of phone number, date, conversation start time, conversation end time. and there around 3476 records in one file and 2477 records in another... and they are not in the same format... so what I did was to sort out all the relative infomation in one file by its phone number, and did the same for the other one then I copy and pasted both lists in a new file.. and did the above comparesion.. nothing showed up or just gives me errors.. I am doing some analysis for our datebase records...... the sample I took out is only the small part.. the real f...

formula numeric input
Is there any way to have Excel assume, as it should, that a number input in a formula means a value? For example, is there a way to change the default so that instead of inputting "+7+8" to calculate 15, that I could just input "7+8". Lotus used to work this way, and I have to believe that Excel would allow the same capability. -- seamaml ------------------------------------------------------------------------ seamaml's Profile: View this thread: