time card
Posted by Don Weinstein on May 09, 2001 12:42 PM
I have made a time card, by day, for which I can get the correct totals, but when I try to get the totals for the week, and weekly O/T I am not getting the correct answers.
To arrive at weekly regular hours my formula is as follows:=if(sum(e5:e14)>hour(40),(40),sum(e5:e14))
this equals 40.00 (I have the cell formatted as number)
To arrive at weekly overtime my formula is:=if(sum(e5:e14)>hour(40),sum(e5:e14)-hour(40),"") this equals 48:31:00 when it should be 8:31:00. This cell is formatted as time "37:30:55".
I need to know how to correct the formulas to give me correct answers?
Thank you in advance for your help.
Don W
Posted by Rick M on May 09, 2001 12:55 PM
Try this.
=IF(SUM(E5:E14)>40,SUM(E5:E14)-40,"")in your overtime cell
Posted by Mark W. on May 09, 2001 12:56 PM
Try these:
(Reg. hrs) =MIN("40:00:00"+0,SUM(E5:E14))
(OT hours) =MOD(SUM(E5:E14),"40:00:00"+0)
Posted by Mark W. on May 09, 2001 1:06 PM
Neglected to say...
Format the cells for these formulas as...
[h]:mm
Posted by Mark W. on May 09, 2001 1:17 PM
Revision of OT formula...
Workaholics ruin my earlier OT formula! Instead,
use the following:
(OT hours) =MAX(0,SUM(C1:C5)-"40:00:00"+0)
Posted by Mark W. on May 09, 2001 1:28 PM
Typos...
(OT hours) =MAX(0,SUM(E5:E145)-"40:00:00")
Posted by Dave Hawley on May 09, 2001 1:39 PM
The best way to do this would be to go to Insert>Name>Define and type: MyHours
Then in the Refers to box type: 1.666667
Click Add then Ok
Now use this formula:
=IF(SUM(E5:E14)>MyHours,SUM(E5:E14)-MyHours,"")
Dave
OzGrid Business Applications
Posted by Don W on May 09, 2001 2:19 PM
Thank you Mark it works great!!!!
Thanks for your fast reply.
