Help me PLEASE

[J.D.J]

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

 

[Ms-Exl-Learner]

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!

 

[David Biddulph]

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(D2<8,0,D2-8) or to =MAX(D2-8,0)
--
David Biddulph

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))

....

 

[Ms-Exl-Learner]

David Sir, Thanks for highlighting my mistake…Today I have learned two
informative information by way of your post.