Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dreamweavn via OfficeKB.com
 
Posts: n/a
Default Calculating Overtime from Hours total

I am working with a bi-weekly timesheet that calculates total hours in
decimal format. I need to calculate the overtime after 8 hours in a day.

C10 shows a clock in time of 8:39
D10 shows a clock out time of 13:00
E10 shows a clock in time of 13:30
F10 shows a clock out time of 17:14
formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of
8.08 hrs worked
H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the
hours in G10 not show hrs in excess of 8.00 at the same time?

Your help is appreciated!!
Dreamweavn
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

If you just want to *display* 8.00 in G10, but leave the value alone,
select G10, then choose Format/Cell/Number/Custom and enter

[<=8]0.00;"8.00"

then use

H10: =MAX(G10-8,0)

If you want instead to have the actual value in G10 be 8 or less:

G10: =MAX(8,(D10-C10+F10-E10)*24)
H10: =MAX(0,(D10-C10+F10-E10)*24-8)


In article ,
"Dreamweavn via OfficeKB.com" wrote:

I am working with a bi-weekly timesheet that calculates total hours in
decimal format. I need to calculate the overtime after 8 hours in a day.

C10 shows a clock in time of 8:39
D10 shows a clock out time of 13:00
E10 shows a clock in time of 13:30
F10 shows a clock out time of 17:14
formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of
8.08 hrs worked
H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the
hours in G10 not show hrs in excess of 8.00 at the same time?

Your help is appreciated!!
Dreamweavn

  #3   Report Post  
Biff
 
Posts: n/a
Default

Probably a typo:

G10: =MAX(8,(D10-C10+F10-E10)*24)


Should be:

G10: =MIN(8,(D10-C10+F10-E10)*24)

Biff

"JE McGimpsey" wrote in message
...
If you just want to *display* 8.00 in G10, but leave the value alone,
select G10, then choose Format/Cell/Number/Custom and enter

[<=8]0.00;"8.00"

then use

H10: =MAX(G10-8,0)

If you want instead to have the actual value in G10 be 8 or less:

G10: =MAX(8,(D10-C10+F10-E10)*24)
H10: =MAX(0,(D10-C10+F10-E10)*24-8)


In article ,
"Dreamweavn via OfficeKB.com" wrote:

I am working with a bi-weekly timesheet that calculates total hours in
decimal format. I need to calculate the overtime after 8 hours in a day.

C10 shows a clock in time of 8:39
D10 shows a clock out time of 13:00
E10 shows a clock in time of 13:30
F10 shows a clock out time of 17:14
formula in G10 is as follows --- =(D10-C10+F10-E10)*24 giving a total of
8.08 hrs worked
H10 needs to show ONLY the .08 of the 8.08 AND is there a way to make the
hours in G10 not show hrs in excess of 8.00 at the same time?

Your help is appreciated!!
Dreamweavn



  #4   Report Post  
Dreamweavn via OfficeKB.com
 
Posts: n/a
Default

Thanks for the quick response! The following does not allow just 8.00 to
appear in G10. It still shows 8.08.

=MAX(8,(D10-C10+F10-E10)*24)

I should have thought a step ahead ...calculating the day itself is fine
but to actaully figure overtime for hours over 40 this does not work, my
fault on that. Can you tell me how to get the following...

G13 shows the total for week 1 (in this case 24.0)
H13 shows the OT total for week 1 (in this case .43)

Of course you can now see why I should have thought a step ahead. OT is not
valid if the hrs worked was only 24, however, lets say the hours were
40.43. How do I get G13 to show 40 total and H13 to show the remaining .43
OT total but NOT show the .43 if G13 is not over 40?

Dreamweavn
  #5   Report Post  
Qwerty
 
Posts: n/a
Default

See Chip Pearson's site.
http://www.cpearson.com/excel/overtime.htm

"Dreamweavn via OfficeKB.com" wrote in message
...
Thanks for the quick response! The following does not allow just 8.00 to
appear in G10. It still shows 8.08.

=MAX(8,(D10-C10+F10-E10)*24)

I should have thought a step ahead ...calculating the day itself is fine
but to actaully figure overtime for hours over 40 this does not work, my
fault on that. Can you tell me how to get the following...

G13 shows the total for week 1 (in this case 24.0)
H13 shows the OT total for week 1 (in this case .43)

Of course you can now see why I should have thought a step ahead. OT is
not
valid if the hrs worked was only 24, however, lets say the hours were
40.43. How do I get G13 to show 40 total and H13 to show the remaining .43
OT total but NOT show the .43 if G13 is not over 40?

Dreamweavn





  #6   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Error in copying, Should be MIN

In article ,
"Dreamweavn via OfficeKB.com" wrote:

=MAX(8,(D10-C10+F10-E10)*24)

  #7   Report Post  
Dreamweavn via OfficeKB.com
 
Posts: n/a
Default

You guys are great!! Thanks for all the help!!

--
Message posted via http://www.officekb.com
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
calculating timesheet, time-in/time-out = total hours & minutes, . Steve Lindsay Excel Worksheet Functions 13 November 8th 06 04:45 PM
need help w/formula for calculating overtime hours jv749297 Excel Worksheet Functions 1 January 17th 05 08:54 PM
Problems calculating total hours LolaK Excel Discussion (Misc queries) 1 December 14th 04 05:23 PM
Subtracting paid hours from unpaid hours ejerry7 Excel Worksheet Functions 5 November 29th 04 05:16 AM
Help! I am stuck calculating Days, Hours, Mins please help OB1 Excel Worksheet Functions 3 November 15th 04 06:17 PM


All times are GMT +1. The time now is 07:11 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"