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 ]

[Aladin Akyurek]

The link asks for username & password. What is up?

[Mitch]

I am not sure, I re-set permission on the file, cleared my cache and didn't get a password prompt.

Try it again and if there is a prompt, just hit ok and if it doesn't show up, let me know so I can track down why it is doing that.

Thanks
Mitch

[Jack in the UK]

Hi

First all my friends check your private messages from me

Hi Mate not to happy hear i have gone to your link and some download went and my virus checker or should i say CHECKERS tripped and went MAD !

Whats happening... Aladin report passwords i report VIRUS and sugget no one touches the link..

Please explain poss this post needs removing otherwise..

Rdgs

[Mitch]

I don't know what you are saying when you say this contains a virus, I just put this up and I don't rum macros or anything. I took the link off and checked it with my virus checker and did not receive anything, I am not sure why you are receiving an error, oh well, just put up to let someone see what I was trying to do, I don't want anyone getting the intention that I am putting viruses out there.

Thanks
Mitch

[Mitch]

=MIN(H17,1+TIME(40,0,0)) . This formula, which assumes a 40-hour week, returns the smaller of two values: the total number of hours worked, or 40 hours. This formula subtracts the number of regular hours from total hours to yield the number of overtime hours.

I want this to work after 8 hours, does anyone know how I can impliment this.

Thanks

[Aladin Akyurek]

On 2002-02-23 15:28, Mitch wrote:
=MIN(H17,1+TIME(40,0,0)) . This formula, which assumes a 40-hour week, returns the smaller of two values: the total number of hours worked, or 40 hours. This formula subtracts the number of regular hours from total hours to yield the number of overtime hours.

I want this to work after 8 hours, does anyone know how I can impliment this.

Thanks

Mitch,

I forgat a bit the lay-out of your worksheet.

In I3 enter (I think it was row 3 where your data for regular days start):

=IF(AND(H3,H3>"8:00"+0),H3-"8:00"+0,0)

and copy down.

Note. The above is not meant for Saturday and Sunday entries.

I also noticed that you check whether there is an entry before you do a substraction. A good thing to do. However, you can shorten the formulas that do such checking like in

=IF(A1,B1-A1,0)

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-02-23 23:18 ]

[Mitch]

Ok, It didn't work. Let me explain better!

Here is a sample layout:

A17
Monday

b17 (IN)
7:30 A

C17 (OUT)
11:30 A

D17 (TOTAL HOURS)
4:00

E17 (IN)
12:30 P

F17 (OUT)
5:30 P

G17 (TOTAL HOURS)
5:00

H17 (TOTAL REGULAR HOURD)
9:00

I17 (OVERTIME HOURS)
------>THIS IS WHERE I NEED TO FIGURE WHAT FUNCTION TO PUT THAT WILL ADD THE 1 HOUR OVERTIME

Hope this explaines better, thanks
Mitch

[ This Message was edited by: Mitch on 2002-02-23 17:14 ]

[Andrew XJ]

The following are the sample formulas for your cells.

C17
=Sum(C17,-B17)

F17
=SUM(F17,E17)

G17
=SUM(C17,F17)

for the rows following you can just use auto fill(select A17:I17 and drag to row 18 and following rows needed)

Hope this helps you

[Aladin Akyurek]

On 2002-02-23 17:06, Mitch wrote:
Ok, It didn't work. Let me explain better!

Here is a sample layout:

A17
Monday

b17 (IN)
7:30 A

C17 (OUT)
11:30 A

D17 (TOTAL HOURS)
4:00

E17 (IN)
12:30 P

F17 (OUT)
5:30 P

G17 (TOTAL HOURS)
5:00

H17 (TOTAL REGULAR HOURD)
9:00

I17 (OVERTIME HOURS)
------>THIS IS WHERE I NEED TO FIGURE WHAT FUNCTION TO PUT THAT WILL ADD THE 1 HOUR OVERTIME

Hope this explaines better, thanks
Mitch

[ This Message was edited by: Mitch on 2002-02-23 17:14 ]

In I17 enter:

IF(AND(H17,H17>"8:00"+0),H17-"8:00"+0,0)

Custom format I17 as [hh]:mm