formula help

[Tammy]

total ABS(L42)+12-ABS(L41)
l42=1:30AM ,l41=5:00pm

lunch==IF(AND(HOUR(K43)<6,HOUR(K43)<18),0,($A1))

this should be total hours worked (5 days) but coming up with
#value!=SUM(B43-12,E43-12,H43-12,K43-12,N43-12)
total sum should work, but...doesn't =SUM(B39-12,E39-12,H39-12,K39-12,
N39-12)

[Sandy Mann]

Tammy,

Would you like to explain a bit more exactly whatit is you are trying to
do - remember we can't see your spreadsheet. However that is NOT an
invitation to post it here as an attachment. Just try to explain it in
text.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Tammy" <[email protected]> wrote in message
news:[email protected]...
> total ABS(L42)+12-ABS(L41)
> l42=1:30AM ,l41=5:00pm
>
> lunch==IF(AND(HOUR(K43)<6,HOUR(K43)<18),0,($A1))
>
> this should be total hours worked (5 days) but coming up with
> #value!=SUM(B43-12,E43-12,H43-12,K43-12,N43-12)
> total sum should work, but...doesn't =SUM(B39-12,E39-12,H39-12,K39-12,
> N39-12)

[Tammy]

I have employees that work 7 to 3:30, 3-11:30, 5 to 9, 5pm to 1:30 am. I am
trying to subtract lunch for anyone working more than 6 hours, then total all
hours worked, all lunches totalled, then a total of all hours worked in the
facility. My problem seems to be the people that work past midnight, although
the daily totals are correct, the weekly totals for each individual does not
work.

"Sandy Mann" wrote:

> Tammy,
>
> Would you like to explain a bit more exactly whatit is you are trying to
> do - remember we can't see your spreadsheet. However that is NOT an
> invitation to post it here as an attachment. Just try to explain it in
> text.
>
> --
> Regards,
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Tammy" <[email protected]> wrote in message
> news:[email protected]...
> > total ABS(L42)+12-ABS(L41)
> > l42=1:30AM ,l41=5:00pm
> >
> > lunch==IF(AND(HOUR(K43)<6,HOUR(K43)<18),0,($A1))
> >
> > this should be total hours worked (5 days) but coming up with
> > #value!=SUM(B43-12,E43-12,H43-12,K43-12,N43-12)
> > total sum should work, but...doesn't =SUM(B39-12,E39-12,H39-12,K39-12,
> > N39-12)
>
>
>

[Sandy Mann]

Well, without trying to duplicate the layout of your sheet:

Start time in Column B2
Finish Time in Column C2
Lunch Time in Column D
Total for the day in Column E

I assume that you are using Excel times like 7:00 AM and 5:00 PM

To get over the problem of times past midnight use

=Start Time-Finish Time + (Finish Time < Start Time)

ie =C2-B2+(C2<B2)
which will calculate the correct time wiether or not it crosses midnight

An alternative is:
=MOD(C2-B2,1)

Now let's see about lunch in D2:
I assume that you want a meal break at any time of the day provided the
worker has worked 6 hours
=IF(MOD(C3-B3,1)>TIME(6,0,0),TIME(1,0,0),0)
For a 1/2 hour break use Time(0,30,0)
This will return either the lunch break time or 0

so the total worked that day in E2 use:
=MOD(C2-B2,1)-D2

When you total the hours custom format the total cell as "[h]:mm" ( without
the quotes)

Incidentally times in XL are a fraction of a day so 1 is one day not 1 hour
so your use of 12, (which I don't understand), means 12 days to XL
The "across midnight" formulas work because if the finish time is smaller
that the start time (C2<B2) this return FALSE which XL converts to 1 in
arithmetic so the formula is: =C2-B1+1 (day). The MOD() version works
because MOD() is returning a positive number

If you need further help then just post back again
--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Tammy" <[email protected]> wrote in message
news:[email protected]...
>I have employees that work 7 to 3:30, 3-11:30, 5 to 9, 5pm to 1:30 am. I am
> trying to subtract lunch for anyone working more than 6 hours, then total
> all
> hours worked, all lunches totalled, then a total of all hours worked in
> the
> facility. My problem seems to be the people that work past midnight,
> although
> the daily totals are correct, the weekly totals for each individual does
> not
> work.
>
> "Sandy Mann" wrote:
>
>> Tammy,
>>
>> Would you like to explain a bit more exactly whatit is you are trying to
>> do - remember we can't see your spreadsheet. However that is NOT an
>> invitation to post it here as an attachment. Just try to explain it in
>> text.
>>
>> --
>> Regards,
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Tammy" <[email protected]> wrote in message
>> news:[email protected]...
>> > total ABS(L42)+12-ABS(L41)
>> > l42=1:30AM ,l41=5:00pm
>> >
>> > lunch==IF(AND(HOUR(K43)<6,HOUR(K43)<18),0,($A1))
>> >
>> > this should be total hours worked (5 days) but coming up with
>> > #value!=SUM(B43-12,E43-12,H43-12,K43-12,N43-12)
>> > total sum should work, but...doesn't
>> > =SUM(B39-12,E39-12,H39-12,K39-12,
>> > N39-12)
>>
>>
>>