formula help #11

Hi! there,
I have a time sheet format for employees and
I just don't know how to create the formula for overtime.
I only want to input finished time in column C.
I don't have much experience in formula function.
The timesheet example is below and please help me to
solve.
Thanks.

       A       B      C      D     E
1  30-01-04  08:00  18:00   1:00
2  31-01-04  08:00  12:00          
3  01-02-04  08:00  14:00         5:00 
4  07-02-04  08:00  17:00   8:00
5  08-02-04  08:00  12:00         4:00

Column (A) is Date
       (B) is started time
       (C) is finished time
       (D) is overtime hour for normal day and Saturday
       (E) is overtime hour for Sunday (different OT rate)
Normal working hour is 08:00 to 17:00
On Saturday 08:00 to 12:00
Off on Sunday.
Lunch time is 12:00 to 13:00
What I want is
formula for normal OT hours in column D and
formula for Sunday & Public holiday OT hours in column E

For column D
(1) count OT hour after 17:00 if the date is Mon to Fri
(2) blank cell if the date is Sunday or no OT or 12:00 on 
Saturday or Public holiday
(3) count OT hour after 13:00 if the date is Saturday

For column E
(1) count OT hour from started time "B" to finished 
time "C" if the date is Sunday & Public holiday
(2) deduct 1 hour for lunch if finished time is more than 
12:00
(3) do not deduct 1 hour if finished time is 12:00
(4) blank cell if the date is Monday~Saturday

Thanks again for your time.
Bradley
0
anonymous (74722)
2/5/2004 7:02:50 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
418 Views

Similar Articles

[PageSpeed] 5

Take a look here for some ideas on handling overtime:

    Overtime hours in timesheets
    http://cpearson.com/excel/overtime.htm



In article <a88901c3ebb6$117e7020$a401280a@phx.gbl>,
 "Bradley" <anonymous@discussions.microsoft.com> wrote:

> Hi! there,
> I have a time sheet format for employees and
> I just don't know how to create the formula for overtime.
> I only want to input finished time in column C.
> I don't have much experience in formula function.
> The timesheet example is below and please help me to
> solve.
> Thanks.
> 
>        A       B      C      D     E
> 1  30-01-04  08:00  18:00   1:00
> 2  31-01-04  08:00  12:00          
> 3  01-02-04  08:00  14:00         5:00 
> 4  07-02-04  08:00  17:00   8:00
> 5  08-02-04  08:00  12:00         4:00
> 
> Column (A) is Date
>        (B) is started time
>        (C) is finished time
>        (D) is overtime hour for normal day and Saturday
>        (E) is overtime hour for Sunday (different OT rate)
> Normal working hour is 08:00 to 17:00
> On Saturday 08:00 to 12:00
> Off on Sunday.
> Lunch time is 12:00 to 13:00
> What I want is
> formula for normal OT hours in column D and
> formula for Sunday & Public holiday OT hours in column E
> 
> For column D
> (1) count OT hour after 17:00 if the date is Mon to Fri
> (2) blank cell if the date is Sunday or no OT or 12:00 on 
> Saturday or Public holiday
> (3) count OT hour after 13:00 if the date is Saturday
> 
> For column E
> (1) count OT hour from started time "B" to finished 
> time "C" if the date is Sunday & Public holiday
> (2) deduct 1 hour for lunch if finished time is more than 
> 12:00
> (3) do not deduct 1 hour if finished time is 12:00
> (4) blank cell if the date is Monday~Saturday
> 
> Thanks again for your time.
> Bradley
0
jemcgimpsey (6723)
2/5/2004 8:40:30 AM
Hi!
McGimpsey,

I had already found the web site 
http://cpearson.com/excel/overtime.htm before
and it couldn't help me much.
I just want to know how to calculate
4 conditions in a cell.
Anyway thanks.

Bradley

>-----Original Message-----
>Take a look here for some ideas on handling overtime:
>
>    Overtime hours in timesheets
>    http://cpearson.com/excel/overtime.htm
>
>
>
>In article <a88901c3ebb6$117e7020$a401280a@phx.gbl>,
> "Bradley" <anonymous@discussions.microsoft.com> wrote:
>
>> Hi! there,
>> I have a time sheet format for employees and
>> I just don't know how to create the formula for 
overtime.
>> I only want to input finished time in column C.
>> I don't have much experience in formula function.
>> The timesheet example is below and please help me to
>> solve.
>> Thanks.
>> 
>>        A       B      C      D     E
>> 1  30-01-04  08:00  18:00   1:00
>> 2  31-01-04  08:00  12:00          
>> 3  01-02-04  08:00  14:00         5:00 
>> 4  07-02-04  08:00  17:00   8:00
>> 5  08-02-04  08:00  12:00         4:00
>> 
>> Column (A) is Date
>>        (B) is started time
>>        (C) is finished time
>>        (D) is overtime hour for normal day and Saturday
>>        (E) is overtime hour for Sunday (different OT 
rate)
>> Normal working hour is 08:00 to 17:00
>> On Saturday 08:00 to 12:00
>> Off on Sunday.
>> Lunch time is 12:00 to 13:00
>> What I want is
>> formula for normal OT hours in column D and
>> formula for Sunday & Public holiday OT hours in column 
E
>> 
>> For column D
>> (1) count OT hour after 17:00 if the date is Mon to Fri
>> (2) blank cell if the date is Sunday or no OT or 12:00 
on 
>> Saturday or Public holiday
>> (3) count OT hour after 13:00 if the date is Saturday
>> 
>> For column E
>> (1) count OT hour from started time "B" to finished 
>> time "C" if the date is Sunday & Public holiday
>> (2) deduct 1 hour for lunch if finished time is more 
than 
>> 12:00
>> (3) do not deduct 1 hour if finished time is 12:00
>> (4) blank cell if the date is Monday~Saturday
>> 
>> Thanks again for your time.
>> Bradley
>.
>
0
anonymous (74722)
2/6/2004 5:34:00 AM
Reply:

Similar Artilces:

Help w/ GRIDLINES EXCEL 2003
GRINDLINES will not show in PRINT PREVIEW nor will they change color to BLACK as I want them to using TOOLS > OPTIONS > VIEW All Background Settings are in DEFAULT since original Install. I am stuck on a report until I get this solved. I am willing to send Spreadsheet via email if you'd like to view my situation. Thank You Ken in page setup check gridlines -- paul remove nospam for email addy! "Caroken" wrote: > GRINDLINES will not show in PRINT PREVIEW nor will they change color to > BLACK as I want them to using TOOLS > OPTIONS > VIEW > &...

Need help building a model in Excel
I'm modeling a two-step industrial process for work Step #1 occurs anytime (date or day of week), step #1 requires 21 days to completion Step #2 follows step #1, but occurs ONLY on Mondays, so, for example,for a particular instance, if (start date of step #1 + 21 days) is a Wed, step #2 doesn't commence until the following Monday. I'm counting pieces (widgets) that will pass from step#1 to step#2, and this model helps to provide estimates for the resources needed to complete step #2 I've having intellectual problems trying to slove this So input is count of widgets ente...

PLS HAVE PROBLEM IN A FORMULA
Hello!!!,,,I need an urgent help in a formula. I have the tables: [Ship] [Invoice] [Payments] IdShip (keyfield) ----->IdShip DateShipped IdInvoice (keyfield) -------> IdInvoice Port AmountPaid AmountInvoiced DatePaid I have a form A, which contains a key field "IdShip". Each IdShip contains more than one invoice, ca...

Formulas containing variable input
Hi guys: I suck at Excel, so I would really appreciate any help. Here's the deal, I have a huge spreadsheet in which each column contains the data pertaining to each month. for example B is january, C is February, and so on... I have a separate sheet where calculations are carried out, and one such calculation requires summing up the columns of data from all previous month including the current month. for instance, if it were march, I would have someth like =Sum(B9:D9), for the month of june i would have =Sum(B9:G9). it's a pain changing the formula manually every month, so is there a...

[ANN] Super Tuesday Update is March 11
Be sure to subscribe to the Entourage Help Blog to get the latest info and help installing Office for Mac 2008 update 12.0.1. Reminder you must have AutoUpdater (MAU) 2.1.1 installed to get notified. <http://blog.entourage.mvps.org/> NetNewsWire is now a free newsreader. Safari also includes RSS feeds. RSS Reader for Mac - NetNewsWire <http://www.newsgator.com/Individuals/NetNewsWire/Default.aspx> -- Diane, Microsoft Mac MVP (MVPs are not Microsoft Employees) Entourage Help Page <http://www.entourage.mvps.org/> Entourage Help Blog <http://blog.entourage.mvps.org/&...

Percentage Formula
This is what I have In cell D - 484 In Cell E - 526 Now in Cell E I want to display the percentage of increase (or decrease) The formula I currenty am using is (D1-E1)/D1 With this I get -9% This can't be correct...becuase it is an increase. What am I doing wrong? Lisa Check your other post. Caribbrz wrote: > > This is what I have > > In cell D - 484 > In Cell E - 526 > > Now in Cell E I want to display the percentage of increase (or decrease) > The formula I currenty am using is > > (D1-E1)/D1 > > With this ...

Formula #11
Hallo ik heb 2 worksheets : Worksheet 1 : Col A : alleen waardes : 0 en 1 Col B & C : Data1 en Data2 Worksheet 2 : In dit worksheet wil ik alleen de waardes uit de kolommen B & C van 'Worksheet 1' maar alleen als in kolom A van 'Worksheet 1' de waarde 1 of hoger is! Wat voor een formule moet ik gebruiken? Als je de vraag niet snapt, ik heb een voorbeeld in een html bestand gezet : http://www.hexan.nl/news/excel.htm Bedankt, Marco J.L. Hi Marco you should probably ask this question either in English (as this is an english NG) or post it to your regional NG....

Cell entry used in formula
I'm using a common lookup formula like the following: =index(B1:B5,match(E1,A1:A5,false),1) Is it possible to write the above formula to reference entries in cell as cell numbers? For example: If cell J1 contains the text "B3" , can a function b written in place of E1 (in the formula) to reference J1 and use B3 (o whatever cell number in in J1)? Hope this makes sense. Thanks Bobb -- Message posted from http://www.ExcelForum.com I'm guessing you might want something like this: =INDEX(B1:B5,MATCH(INDIRECT(J1),A1:A5,FALSE),1) Your example is confusing because you used ...

Formula that returns column header after comparison
I have numeric data in 4 different columns. Each column has a different label. I want to compare the numbers in each column on a row by row basis and have the column label of the lowest value inserted in a different column at the end of the row. Anyone have suggestions on the best way to accomplish that? Thanks in advance! Cheers Drew Say your data is in A1 to D100, with labels in Row1. In say E2, try this formula: =INDEX($A$1:$D$1,MATCH(MIN(A2:D2),A2:D2,0)) Copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep ...

Formula Question 01-14-10
When I use the function arguments it says if(ID606="Y" is true when ID606 is blank .WHY? =IF(ID606="Y",1,2) there must be something in your cell ID606 then...i just checked and i get false and 2 for answers... "oldjay" wrote: > When I use the function arguments it says if(ID606="Y" is true when ID606 is > blank .WHY? > > =IF(ID606="Y",1,2) I put a new formula =if(C1="Y",1.2) in A!. I get the same result. I even deleted C1 just to be sure there was nothing in it. Now I added a new sheet to thi...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! Look for =workday() in Excel's help. (Also part o...

Loop help
Hello all, I created (recorded) a macro to sort and subtotal raw data with the following: Sub SubtotalBalSheet() ' ' SubtotalBalSheet Macro ' Macro recorded 10/15/2004 by A24479 ' ' Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "G/L" Range("B1").Select ActiveCell.FormulaR1C1 = "Branch" Range("C1").Select ActiveCell.FormulaR1C1 = "Description" Range("D1").Select ActiveCell.FormulaR1C1 = "Current Month&...

Unable to open money
I installed Money 2004 in September on my new Dell computer. It has worked flawlessly - until last week! Now, when I attempt to open it I get: "Unable to initialize a required Money component. Please reinstall Money from your original product disks." When I click on the "OKAY" box, I get: "Do you want to check the installation of Money 2004?" When I click on the "OKAY" box, I get: "Finished! Money 2004 Installation Checker did not find any problems with this installation." When I click on the "OKAY" box, it closes. I get ...

can you help me with an invoice
I am trying to get a sample invoice Hi Try http://office.microsoft.com/en-us/templates/CT011377071033.aspx -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "Steve B" wrote: > I am trying to get a sample invoice ...

what formula to use to calculate piece / time
Ok, I have a column in excel that tells me the piece count that people do, they have a start time column and end time column and the time that it took to do that is figured out by: =TEXT(J4-I4,"h:mm") in column K.. the count piece is in column H. How do I divide the count, say is column H reads 1500, and total time it took to do that was 2:00 2 hours, how do you divide 1500 by that time value ? or am I thinking it right ? You have to multiply the time by 24 to get hours as a decimal number of hours -- time is a fraction of a day (24 hours = 1 day).. Time started I4: 1...

Help! Cannot delete email
I have received 2 different emails. One keeps showing up every hour over & over again in Junk e-mail folder. Another email is doing the same in my Norton Anti-Spam folder. The message has been deleted from the mail server but problem will not go away. I have to delete 100's every day of these What can I do?? How do you know that its the *same mail msg* and not a new msg that is the same. Perhaps the email addy you used in this post is your real email, and as such will have been harvested for spam <goner@ev1.net> wrote in message news:rgvag2tl4njnupndki9c55j5vh5bj4clii@4ax.com.....

Formula Needed for Corresponding Blank Cell
Hi All, Well that didn't work: =IF($B$7:$B$689<>"",IF($W$7:$X$689="","SOMETHING IS MISSING",""),"") And neither did this: =IF($B$7:$B$689<>"",IF(OR($W$7:$W$689=""),OR($X$7:$X$689=""),OR($Y$7:$Y $689="")),"SOMETHING IS MISSING",""),"") No laughing. I'm trying. And I did use Ctrl-Shift-Enter. It's supposed to catch blank cells in columns W, X, or Y if column B in the same row isn't blank. Column B is a date and W, X and Y are ...

Help with Lookup formula
I have a three columns of data, column A has part number, column B has cost, column C has company name. What I would like to be able to do is for a formula to lookup 'company1' and give me a total cost of the parts. Is this an easy thing to do? Hi Graham, Try this: =SUMIF(C1:C10,"company1",B1:B10) Regards, KL "Graham" <someone@somewhere.com> wrote in message news:%23EvZqXwpFHA.2976@TK2MSFTNGP12.phx.gbl... >I have a three columns of data, column A has part number, column B has >cost, column C has company name. What I would like to be able to...

Outlook on XP is showing emails but not any appointments, help!
To elaborate, I have configured my outlook client so that it receives/sends emails and that works, but I can't see any of the appointments on my calendar. I have outlook set up at work, and that works fine, but now trying to do it from home. thanks! ...

Help!! Where are my Page Breaks for Page 2,3 etc.
I use Excell for XP and want to add Pagebreaks. Only Page 1 is visible after Menu > View > Pagebraks. I canot add braks with selecting row/column and rightclick. No result after both setting up Print Area and reset Print Area. -- Thank you for your help. Bart Remove X from my e-mail address to send me an e-mail Quite la X de mi direcci�n para enviarme correo electr�nico ____________________ 1.. Bart, does this help? On the View menu, click Page Break Preview. Do one of the following: View page breaks Manually inserted page breaks appear as solid lines. Dashed lines indicat...

Formula to reference another cell in a worksheet
Column G is filled with numbers which represent Rows in my worksheet. I want Column H to equal the contents of Column A Row ? which is referenced in Column G. Example: G1 is 1043, I want H1 to be equal to A1043. What formula can I use to fill column F to do this automatically. Thank you! Heather =indirect("A" & G1) -- HTH... Jim Thomlinson "HeatherJ" wrote: > Column G is filled with numbers which represent Rows in my worksheet. I want > Column H to equal the contents of Column A Row ? which is referenced in > Column G. >...

SUMPRODUCT help
Hi - some years ago I needed a formula that would count rows where certain columns were certain values. ie. in english, it'd be something like if col a = 1 and col d <8000 and col f > 5 then count the row. Someone sent me a SUMPRODUCT ( I think it was that) formula that did the trick. I can't find that formula - the xls file it was in is now gone, and once I again I need the same type of formula. I can't use DCOUNT since the cols to check are not sequential (next to eachother). Anyone know the trick of using SUMPRODUCT or some other formula (in case I remember is wro...

help with Xl time sheet
I am creating a time sheet. I am wondering if there is a short cut to enter the staffs time. Right now I copy and paste the time in but was hopeing there is a better way. here what I am working with part time who work 4 days in a row full time who work 5 days in a row I have early afternoon and late shifts My work week is 6 days. Colume "B" is mon "C" is tues... "G" is Sat. can I make 6 short cut keys to fit all my needs? I was thinking something like type f1 in colum "C" it would enter in 5 days of early shift(tuesday colume "C" to sat colum...

New 2k Exchange w/ existing 2k Exchange Server Problem HELP!!!!!!!
I just installed a new 2000 exchange server on my domain and I am having a problem connecting to it. I have an existing 2000 Exchange server called "BOSS" and my new one is called "exchsrvr" they are both online I am moving my mail box over to the new server "exchsrvr" and I just tried to sync my outlook up to the new one by entering the name of the new server "exchsrvr" and then my name and clicking "Check name" and once i click the "check name" button the server name changes back to my old exchange server "BOSS" how...

Formula's not calculatin in Excel 2007
Hi, Earlier if i had formulae in excel , if i entered any value(for ex a10 was sum of a1 to a8 and if i entered any value in a4 automatically a10 used to show 4 ie) the resulting value cell used to automatically calculate as usual. But now i am observing from few days that regardless of any excel i open , if i enter any value where formula exist the values are not getting automatically calculated. I have to manually go to the cell which has formula and when i double click and press enter the formula works. This is happening for any sheet i open Is it because that any settings have c...