Need help with formulas
 

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 which
desire the result gives it in hours. When I turn to him the hours in
general numbers it changes to decimal. That is, if I have an employee
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30PM
and it leaves to the 5:30PM formulates it present says to me that the
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thanks


--
LISVET
------------------------------------------------------------------------
LISVET's Profile: http://www.excelforum.com/member.php...o&userid=27146
View this thread: http://www.excelforum.com/showthread...hreadid=466608

LISVET Wrote:
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 which
desire the result gives it in hours. When I turn to him the hours in
general numbers it changes to decimal. That is, if I have an employee
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30PM
and it leaves to the 5:30PM formulates it present says to me that the
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thanks

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466608

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
--
Gary''s Student


"LISVET" wrote:


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 which
desire the result gives it in hours. When I turn to him the hours in
general numbers it changes to decimal. That is, if I have an employee
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30PM
and it leaves to the 5:30PM formulates it present says to me that the
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thanks


--
LISVET
------------------------------------------------------------------------
LISVET's Profile: http://www.excelforum.com/member.php...o&userid=27146
View this thread: http://www.excelforum.com/showthread...hreadid=466608

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(B20,E20))

This compensates for shifts that work past midnight.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LISVET" wrote in
message ...

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 which
desire the result gives it in hours. When I turn to him the hours in
general numbers it changes to decimal. That is, if I have an employee
who enters to work 7AM and leaves lunch to the 12PM, it returns 12:30PM
and it leaves to the 5:30PM formulates it present says to me that the
total is 10:00 A.M.. How this total I turn it to 10 (hours)

Help, Thanks


--
LISVET
------------------------------------------------------------------------
LISVET's Profile:
http://www.excelforum.com/member.php...o&userid=27146
View this thread: http://www.excelforum.com/showthread...hreadid=466608