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

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

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

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

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

Error in copying, Should be MIN

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

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

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

--
Message posted via http://www.officekb.com