Thanks:  0
Likes:  0

1. Thanks Aladin Akyurek, That did the trick!

There is one more thing and the time card is complete! and will be ready to utilize for Monday's start time and make my life much easier for the 23 employee's I have to track.

In H17 (Total Regular Hours) I would like it to add G17 and D17 and if the amount is less than [h.mm]8:00 put that amount in H17 (eg: 7:43) and if it is greater than [h.mm]8:00, just put [h.mm]8:00 in H17, because the overtime hours are already put in I 17.

Thanks alot!
Mitch

2. On 2002-02-24 05:47, Mitch wrote:
That did the trick!

There is one more thing and the time card is complete! and will be ready to utilize for Monday's start time and make my life much easier for the 23 employee's I have to track.

In H17 (Total Regular Hours) I would like it to add G17 and D17 and if the amount is less than [h.mm]8:00 put that amount in H17 (eg: 7:43) and if it is greater than [h.mm]8:00, just put [h.mm]8:00 in H17, because the overtime hours are already put in I 17.
Mitch,

Your last request would give us a circular reference. That's why I asked you sending me your workbook in order to start over. Don't be alarmed!

Here is how your worksheet for Weekly Time Record looks like including everything:

{"Day","In","Out","Total Time","In","Out","Total Time","Regular Hours","Overtime";
"Saturday",0.291666666666667,0.458333333333333,0.166666666666667,0,0,0,0,0.166666666666667;
"Sunday",0,0,0,0,0,0,0,0;
"Monday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
"Tuesday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.738194444444444,0.154861111111111,0.333333333333333,0.00 902777777777769;
"Wednesday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
"Thursday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
"Friday",0.291666666666667,0.5,0.208333333333333,0.583333333333333,0.708333333333333,0.125,0.333333333333333,0;
0,0,0,0,0,0,"Total Hours",1.60416666666667,0.175694444444444}

Note. Zeroes stand for empty cells and funny looking numbers are times so as they are internally represented by Excel.

The above data along with calculations are in A9:I17.

In D10 enter:

=IF(AND(ISNUMBER(B10),ISNUMBER(C10)),C10+(C10
[ copy down till the Friday row ]

In G10 enter:

=IF(AND(ISNUMBER(E10),ISNUMBER(F10)),F10+(F10
[ copy down till the Friday row ]

In H10 enter:

=IF(OR(A10="Saturday",A10="Sunday"),0,IF(SUM(D10,G10)>"8:00"+0,"8:00"+0,SUM(D10,G10)))

[ copy down until the Friday row ]

In H11 enter: =SUM(H10:H16)

In I10 enter:

=IF(OR(A10="Saturday",A10="Sunday"),SUM(D10,G10),IF(SUM(D10,G10)>"8:00"+0,SUM(D10,G10)-"8:00"+0,0))

[ copy down this till the Friday row ]

Note 1. I had to change the earlier formula for I in order to avoid circular references which were bound to occur because of your latest request.

Note 2. As you wanted, column H will show only totals of regular work hours at a max of 8:00 involving a day. Any overtime hours will be shown in I.

In I11 enter: =SUM(I10:I16)

All cells where time diffs and totals are calculated are custom formatted as [hh]:mm.

I'll send you the modified workbook via e-mail.

3. Your Amazing!! This works perfect and is just what I needed.

4. ## Re: Time sheet

I have this problem where my spreadsheet is calculating worked hours, regular hours, and overtime hours. Any hours over 8 automatically goes into overtime hours which is fine, but once you go over 40 hours, the hours still split between regular hours and overtime hours. I need the formulas to figure 40 hours in the work week, EVERYTHING else is OT.

Is there any way for me to attach my spreadsheet?

5. ## Re: Time sheet

Originally Posted by miraclmoore
I have this problem where my spreadsheet is calculating worked hours, regular hours, and overtime hours. Any hours over 8 automatically goes into overtime hours which is fine, but once you go over 40 hours, the hours still split between regular hours and overtime hours. I need the formulas to figure 40 hours in the work week, EVERYTHING else is OT.

Is there any way for me to attach my spreadsheet?
Here is the link to my file:

http://www.mediafire.com/?l64awr6h84m3rlo

Any and all help would be greatly appreciated.

Happy New Years!

6. ## Re: Time sheet

Is there anyone that can help me with this calculation?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•