Time sheet

Mitch

New Member
Joined
Feb 22, 2002
Messages
7
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top