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 <[email protected]>,
"Dreamweavn via OfficeKB.com" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Dreamweavn via OfficeKB.com" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
"Dreamweavn via OfficeKB.com" <[email protected]> wrote:

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

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

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