Time Sheet

Hello All,

I am writing a time sheet for work.
C 11 thru I 11 has the actual hours worked each day J 11 has the
total hours worked for the week (sum c11:I11). What I am trying to do
is have J11 show only 40 hours if the work week was over 40 hours. I
want to have the overtime hours in the cell below on J12.

I have entered a formula in J12 as sum (c11:I 11 -40) this works good
if there is overtime but shows a neg number if no overtime.

Can anyone help with the formula in these two cells



Thanks
Gary Davis

Peo
Wed Apr 27 00:26:53 CDT 2005

Try

=MIN(40,SUM(C11:I11))

in J12

=MAX(0,SUM(C11:I11)-40)



--
Regards,

Peo Sjoblom


"Gary Davis" <gdavis@commspeed.net> wrote in message
news:ag6u61ppd2en8k4m4tpvrs6m36mq7rr1n6@4ax.com...
> Hello All,
>
> I am writing a time sheet for work.
> C 11 thru I 11 has the actual hours worked each day J 11 has the
> total hours worked for the week (sum c11:I11). What I am trying to do
> is have J11 show only 40 hours if the work week was over 40 hours. I
> want to have the overtime hours in the cell below on J12.
>
> I have entered a formula in J12 as sum (c11:I 11 -40) this works good
> if there is overtime but shows a neg number if no overtime.
>
> Can anyone help with the formula in these two cells
>
>
>
> Thanks
> Gary Davis
>
>

Franz
Wed Apr 27 04:01:09 CDT 2005

"Gary Davis" <gdavis@commspeed.net>ha scritto nel messaggio
ag6u61ppd2en8k4m4tpvrs6m36mq7rr1n6@4ax.com

> Hello All,
>
> I am writing a time sheet for work.
> C 11 thru I 11 has the actual hours worked each day J 11 has the
> total hours worked for the week (sum c11:I11). What I am trying to do
> is have J11 show only 40 hours if the work week was over 40 hours. I
> want to have the overtime hours in the cell below on J12.
>
> I have entered a formula in J12 as sum (c11:I 11 -40) this works good
> if there is overtime but shows a neg number if no overtime.


Check out Chip Pearson's site
http://www.cpearson.com/excel/overtime.htm
http://www.cpearson.com/excel/DateTimeEntry.htm


--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

Gary
Wed Apr 27 21:22:52 CDT 2005

Thank you all very much. It is a big help

On Wed, 27 Apr 2005 11:01:09 +0200, "Franz" <fra68ve@InVento.it>
wrote:

>"Gary Davis" <gdavis@commspeed.net>ha scritto nel messaggio
>ag6u61ppd2en8k4m4tpvrs6m36mq7rr1n6@4ax.com
>
>> Hello All,
>>
>> I am writing a time sheet for work.
>> C 11 thru I 11 has the actual hours worked each day J 11 has the
>> total hours worked for the week (sum c11:I11). What I am trying to do
>> is have J11 show only 40 hours if the work week was over 40 hours. I
>> want to have the overtime hours in the cell below on J12.
>>
>> I have entered a formula in J12 as sum (c11:I 11 -40) this works good
>> if there is overtime but shows a neg number if no overtime.
>
>
>Check out Chip Pearson's site
>http://www.cpearson.com/excel/overtime.htm
>http://www.cpearson.com/excel/DateTimeEntry.htm