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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help me PLEASE
What i am trying to do is a hours report to keep track of employees hours. I
will have three sheet..sheet one will be for week 1..sheet 2 will be week 2 and sheet three will be total hours for employees after the two weeks are done. example: Oct 1st in time out time total hours John doe (a1) 5:00(b1) 14:30(c1) 9.5 (d) i need the formula to get it to avg the hours to get 9.5 i know that it will be the same for week two. now on the third page it will be set up like this week 1 overtime week 2 over time total hours john doe 40 3.5 40 0 83.5 thanks for looking |
#2
|
|||
|
|||
Help me PLEASE
Try this...
Make your both Sheet1 and Sheet 2 Data Like this... Sheet 1 & 2 Data A B C D E Oct 1st In Time Out Time Total Hrs OT John Doe 5:00 AM 2:30 PM 9.5 1.5 Format the B&C Column as Time. D&E Columns should be formatted as General. In Total Hrs that is D2 put this formula =((C2-B2)*(24)) Add one more field OT in E. For OT calculation that is in E2 put this formula =IF((D2-8)0,"0",(D2-8)) Like this arrange your data for sheet2 also. Sheet 3 For getting the desired results in Sheet3 in the below manner, pls follow the steps given below:- Name Week 1 OT Week 2 OT Total John Doe 40 3.5 40 0 83.5 For doing total of Week1 (Sheet1 Total Hrs values) that is in B2 cell put this formula. =SUMIF(Sheet1!A:A,Sheet3!A2,Sheet1!D) For doing total of OT (Sheet1 OT values) that is in C2 cell put this formula. =SUMIF(Sheet1!A:A,Sheet3!A2,Sheet1!E:E) For doing total of Week2 (Sheet2 Total Hrs Value) that is in D2 cell put this formula. =SUMIF(Sheet2!A:A,Sheet3!A2,Sheet2!D) For doing total of OT (Sheet2 OT Value) that is in E2 cell put this formula. =SUMIF(Sheet2!A:A,Sheet3!A2,Sheet2!E:E) finally in F2 put this formula for doing total of Sheet3 week1, OT and Week2 & OT values. =SUM(B2:E2) Hope this is what you want. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "J.D.J" wrote: What i am trying to do is a hours report to keep track of employees hours. I will have three sheet..sheet one will be for week 1..sheet 2 will be week 2 and sheet three will be total hours for employees after the two weeks are done. example: Oct 1st in time out time total hours John doe (a1) 5:00(b1) 14:30(c1) 9.5 (d) i need the formula to get it to avg the hours to get 9.5 i know that it will be the same for week two. now on the third page it will be set up like this week 1 overtime week 2 over time total hours john doe 40 3.5 40 0 83.5 thanks for looking |
#3
|
|||
|
|||
Help me PLEASE
In general I would not advise outputting a text string in a cell where a
number is expected. Better to get rid of the quote marks and change your =IF((D2-8)0,"0",(D2-8)) to =IF((D2-8)0,0,(D2-8)) or more simply to =IF(D28,0,D2-8) or to =MAX(D2-8,0) -- David Biddulph "Ms-Exl-Learner" wrote in message ... Try this... Make your both Sheet1 and Sheet 2 Data Like this... Sheet 1 & 2 Data A B C D E Oct 1st In Time Out Time Total Hrs OT John Doe 5:00 AM 2:30 PM 9.5 1.5 Format the B&C Column as Time. D&E Columns should be formatted as General. In Total Hrs that is D2 put this formula =((C2-B2)*(24)) Add one more field OT in E. For OT calculation that is in E2 put this formula =IF((D2-8)0,"0",(D2-8)) .... |
#4
|
|||
|
|||
Help me PLEASE
David Sir, Thanks for highlighting my mistake…Today I have learned two
informative information by way of your post. -------------------- (Ms-Exl-Learner) -------------------- "David Biddulph" wrote: In general I would not advise outputting a text string in a cell where a number is expected. Better to get rid of the quote marks and change your =IF((D2-8)0,"0",(D2-8)) to =IF((D2-8)0,0,(D2-8)) or more simply to =IF(D28,0,D2-8) or to =MAX(D2-8,0) -- David Biddulph "Ms-Exl-Learner" wrote in message ... Try this... Make your both Sheet1 and Sheet 2 Data Like this... Sheet 1 & 2 Data A B C D E Oct 1st In Time Out Time Total Hrs OT John Doe 5:00 AM 2:30 PM 9.5 1.5 Format the B&C Column as Time. D&E Columns should be formatted as General. In Total Hrs that is D2 put this formula =((C2-B2)*(24)) Add one more field OT in E. For OT calculation that is in E2 put this formula =IF((D2-8)0,"0",(D2-8)) .... |
Thread Tools | |
Display Modes | |
|
|