Need help with formulas-How this total I turn it to 10

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

[BenjieLop]

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.

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

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LISVET" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>