Need help with formulas

L

LISVET

undefined

I am trying to create a worksheet to calculate the worked hours daily
It formulates it that I have managed to do that but approaches whic
desire the result gives it in hours. When I turn to him the hours i
general numbers it changes to decimal. That is, if I have an employe
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30P
and it leaves to the 5:30PM formulates it present says to me that th
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thank
 
B

BenjieLop

LISVET said:
undefined

I am trying to create a worksheet to calculate the worked hours daily
It formulates it that I have managed to do that but approaches whic
desire the result gives it in hours. When I turn to him the hours i
general numbers it changes to decimal. That is, if I have an employe
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30P
and it leaves to the 5:30PM formulates it present says to me that th
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thanks
Click to expand...

Try this ...

=IF(((A1-B1)+(C1-D1))*24<0,0,((A1-B1)+(C1-D1))*24

where

A1 = time in to start the day
B1 = time out (for lunch)
C1 = time in (from lunch)
D1 = time out for the day

BTW, all your cells should be time formatted.

Hope this helps you.

Regards
 
G

Gary''s Student

Just an example:
In A1 through D1 put:

7:00:00 AM 12:00:00 PM 12:30:00 PM 5:30:00 PM

In E1 put:
=D1-A1-(C1-B1)
you will see 10:00 (if you format E1 as Time 13:30
 
R

Ragdyer

Days of the week in Column A,
Row1 has header labels starting in Column B:
Start, Out Lunch, In Lunch, End

Enter this in F2, with F2 formatted as number:

=(((E2-B2+(E2<B2))-(D2-C2+(D2<C2)))*24)*(AND(B2>0,E2>0))

This compensates for shifts that work past midnight.
 
Top