Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jongyrocka
 
Posts: n/a
Default help with calculating overtime in a time sheet


Hi Guys,
I'm sure the question has been asked from time to time, but this one
has got me a bit stumped, and looking at other peoples questions, just
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, a
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 76
anyway)
another cell that works out time and half (which is normal time that
goes over 76)
and another cell that works out the double time.


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

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


Cheers
Nico


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




--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile:
http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

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


jongyrocka wrote:
Hi Guys,
I'm sure the question has been asked from time to time, but this one
has got me a bit stumped, and looking at other peoples questions, just
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, a
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 76
anyway)
another cell that works out time and half (which is normal time that
goes over 76)
and another cell that works out the double time.


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

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


Cheers
Nico


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




  #3   Report Post  
Ltat42a
 
Posts: n/a
Default


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 Wrote:
Hi Guys,
I'm sure the question has been asked from time to time, but this one
has got me a bit stumped, and looking at other peoples questions, just
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, a
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 76
anyway)
another cell that works out time and half (which is normal time that
goes over 76)
and another cell that works out the double time.


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

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


Cheers
Nico


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




--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #4   Report Post  
jongyrocka
 
Posts: n/a
Default


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?!


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile: http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #5   Report Post  
Daminc
 
Posts: n/a
Default


I'm not sure how to do it in Excel (I'm just beginning myself) but I
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.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=467159



  #6   Report Post  
jongyrocka
 
Posts: n/a
Default


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile: http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #7   Report Post  
Daminc
 
Posts: n/a
Default


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 E22400 THEN -2400 ELSE END

or something along those lines ???


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #8   Report Post  
jongyrocka
 
Posts: n/a
Default


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile: http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #9   Report Post  
Daminc
 
Posts: n/a
Default


I just amended my last post.

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


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=467159

  #10   Report Post  
jongyrocka
 
Posts: n/a
Default


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?!


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile: http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159



  #11   Report Post  
Posted to microsoft.public.excel.misc
confused
 
Posts: n/a
Default help with calculating overtime in a time sheet

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


"jongyrocka" wrote:


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile: http://www.excelforum.com/member.php...o&userid=27200
View this thread: http://www.excelforum.com/showthread...hreadid=467159


  #12   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default help with calculating overtime in a time sheet

=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)


"confused" wrote in message
...
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


"jongyrocka" wrote:


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile:
http://www.excelforum.com/member.php...o&userid=27200
View this thread:
http://www.excelforum.com/showthread...hreadid=467159



  #13   Report Post  
Posted to microsoft.public.excel.misc
Arvi Laanemets
 
Posts: n/a
Default help with calculating overtime in a time sheet

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


"confused" wrote in message
...
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


"jongyrocka" wrote:


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile:

http://www.excelforum.com/member.php...o&userid=27200
View this thread:

http://www.excelforum.com/showthread...hreadid=467159




  #14   Report Post  
Posted to microsoft.public.excel.misc
Rich
 
Posts: n/a
Default help with calculating overtime in a time sheet


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

thanks thats helped a lot


"Arvi Laanemets" wrote:

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


"confused" wrote in message
...
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


"jongyrocka" wrote:


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


--
jongyrocka
------------------------------------------------------------------------
jongyrocka's Profile:

http://www.excelforum.com/member.php...o&userid=27200
View this thread:

http://www.excelforum.com/showthread...hreadid=467159





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i secuire Sheet Same time Update the Pivot Table Michael H.M.Mikhail Excel Worksheet Functions 0 July 25th 05 08:18 AM
Auto Calculating Time Sheet Durocdog Excel Worksheet Functions 1 June 7th 05 09:54 AM
Excel sheet for workout jogging time and graph Trevor New Users to Excel 1 March 7th 05 04:54 PM
How do I set up a formula on a time sheet to calculate time in 1/. gschmid Excel Discussion (Misc queries) 2 January 18th 05 02:48 PM
need help w/formula for calculating overtime hours jv749297 Excel Worksheet Functions 1 January 17th 05 08:54 PM


All times are GMT +1. The time now is 09:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"