help with calculating overtime in a time sheet

[jongyrocka]

Hi Guys,
I'm sure the question has been asked from time to time, but this on
has got me a bit stumped, and looking at other peoples questions, jus
confuses me even more!

so basically I just wanna be able to enter times say from
0600 am till 1953 with a .5 lunch break

Basically what I want from this, is a cell for normal hours up to 10,
cell for overtime hours after 10 and a total.

i've managed to get a total, and overtime hours, but not normal hours!

Then for the totals
I want a cell that works out normal hours (which should always be 7
anyway)
another cell that works out time and half (which is normal time tha
goes over 76)
and another cell that works out the double time.


Sounds a bit weird, but basically we usually do long days...we ge
double time after 10 hours in a day. and time and half after 76hrs in
fortnight..

I have a copy of mytimesheet of what i've achieved so far, so if anyon
could maybe help me, download it, edit it, and maybe mail it back to m
would be awesome!
with any suggestions!!


Cheers
Nico


http://members.iinet.net.au/~nico69/time_sheet.xls

(e-mail address removed)

 

[Roger Govier]

Hi

In cell E2 =MIN(10,((D2-B2)*24-C2))
In Cell F2 =MAX(0,((D2-B2)*24-C2-10))
In cell H2 =E2+F2
Copy down range as appropriate. Format cells E2:H17 to 2 places decimal.

Regards

Roger Govier

 

[Ltat42a]

Here's what I use, hope it helps,

For a two week pay period, I calculate the number of hours down a
column (c5:c11; & c14:c20). I add these two together to give me the
total number of hours worked for the two week pay period.

I use this to calculate any hours worked over 80 (time & 1 half) -
=IF(L23>=80,SUM(L23-80),"0")

My end result is that I get a total for the 80 hours and (if any),
overtime hours. I then use my rate of pay to determine my pay including
OT - if any.


...Ltat42a

 

[jongyrocka]

thanks heaps guys...now the only problem is, if i put in a time from say
23:00 till 03:00 i get a negative result...

How can i over come this?!

 

[Daminc]

I'm not sure how to do it in Excel (I'm just beginning myself) but
would assume that you could write something that describes:

Hours='TimeB - Time A'
IF Hours = a minus number then *-1 else keep answer.

Something like that

 

[jongyrocka]

hmm...not sure...
either way, i have to include it into the formula's that Russle Govier
posted

 

[Daminc]

In Excel the Absolute value is calculated by using the ABS function so
maybe something like:

In cell E2 =MIN(10,(*(ABS*(D2-B2)*+24)**24-C2))

or wherever that minus number might be calculated



Sorry, I don't think that would work

0300 - 2300 = -2000
-2000 + 2400 = 0400 (which would work)

2300 - 2000 = 0300 (which is what you want)
0300 + 2400 = 2700 (which isn't what you want)

Perhaps with In cell E2 =MIN(10,((D2-B2)+2400*24-C2))

Then IF E2>2400 THEN -2400 ELSE END

or something along those lines ???

 

[jongyrocka]

I gave it a shot but no cigar...

I'm pretty sure I've got to include some more of these's () with a < in
it somewhere

 

[Daminc]

I just amended my last post.

(Please bare in mind that I can do the math but I'm a novice at Excel)

 

[jongyrocka]

Ok...
It think i've done it!!

How realiable it is, i dunno... but it works

=MIN(10,((D7-B7+(D7<B7))*24-C7))
=MAX(0,((D12-B12+(D12<B12))*24-C12-10))


can anyone else confirm that this is ok to use?

 

[Guest]

am havint the same problem .but use a 48he clock to overcome it for now

so i enter the time as 23:00 to 27:00

excell does not like working out "night hours" so without over inflating the
workbook with excessive formulas this works

 

[Peo Sjoblom]

=MOD(end-start,1)


will take care of hours after midnight

start 18:00
end 04:00


where A1 holds 18:00 and B1 04:00

=MOD(B1-A1,1)

returns 10:00

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Arvi Laanemets]

Hi

Start time: A2=23:00
End time : B2= 3:00
Working hours: C2=B2-A2+(B2<A2)
C2 returns 4:00 (the formula returns right time intervall, until it remains
<24:00 - whenever end time < start time, midnight rollover is counted)


Arvi Laanemets

 

[Guest]

lol,, thanks, guess i was overlooking the easier option and looking at more
complicated formulas,,,

thanks thats helped a lot