Time sheet

[Mitch]

I am kind of new with excel. I have developed a weekly time sheet. in the Cell H, I have it to where it totals per day hours, exe) 8:45(h.mm), I would like to add a cell I to automatically notice that after 8 hours to put in the overtime hours after 8 hours into cell I.

Also, weekends are automatic overtime.

Does anyone know what kind of funtion to put into to achieve this.

Thanks, wish I had found this forum a couple of days ago, I was going nuts with a certain funtion and took me 2 days to figure it out!
This message was edited by Mitch on 2002-02-23 14:38
This message was edited by Mitch on 2002-02-23 15:01

 

[Mitch]

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

 

[Aladin Akyurek]

On 2002-02-24 05:47, Mitch wrote:
<snip> 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.
<snip>

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.00902777777777769;
"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<B10)-B10,0)

[ copy down till the Friday row ]

In G10 enter:

=IF(AND(ISNUMBER(E10),ISNUMBER(F10)),F10+(F10<E10)-E10,0)

[ 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.

Aladin

 

[Mitch]

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

Thanks for all your help

 

[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?

 

[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!

 

[miraclmoore]

Is there anyone that can help me with this calculation?