I have set up a time sheet for employees to fill out on the computer. I need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything beyond
that to go in the OVERTIME column. So in other words, if I work 10 hours in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what formulas to use
to make this happen automatically upon entering the hours worked.

you can't really have one where you enter 10 and it changes to 8 and 2 in
an extra column - well you can but it's not the easiest way to work with
the data

Best to have at least

A1 "Hours Worked"
B1 "Normal"
C1 "Overtime"
Then enter data in A2, A3 etc
with the following formula in B2 and C2

B2 = min(A1,8) ie if you work less it shows the correct hours
C2 = max(A1,8)-8 ie the number of hours more than 8
The formula in B2 and C2 can be copied down as you enter more data

Steve

On Mon, 21 Aug 2006 23:33:35 +0100, KimC
wrote:

I have set up a time sheet for employees to fill out on the computer. I
need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything
beyond
that to go in the OVERTIME column. So in other words, if I work 10 hours
in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what formulas
to use
to make this happen automatically upon entering the hours worked.

The MAX and MIN functions should do what you need. Look them up in help for
more info.

For Straight Time:

=MIN(yoursum,8)

For Overtime:

=MAX(yoursum-8,0)

(replace yoursum with whatever formula you're currently using)

HTH,
Elkar


"KimC" wrote:

I have set up a time sheet for employees to fill out on the computer. I need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything beyond
that to go in the OVERTIME column. So in other words, if I work 10 hours in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what formulas to use
to make this happen automatically upon entering the hours worked.

I don't have a formula at all currently. I have six fields, in the following
order:
In, Break Taken, Out, In, Break Taken, Out
I need to somehow exclude the "Break Taken" columns, as they are required
and paid ten-minute breaks. So I appreciate the help so far very much, but
can you also assist me in the rest of the formula, or is what you provided so
far the only formula I need?

"Elkar" wrote:

The MAX and MIN functions should do what you need. Look them up in help for
more info.

For Straight Time:

=MIN(yoursum,8)

For Overtime:

=MAX(yoursum-8,0)

(replace yoursum with whatever formula you're currently using)

HTH,
Elkar


"KimC" wrote:

I have set up a time sheet for employees to fill out on the computer. I need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything beyond
that to go in the OVERTIME column. So in other words, if I work 10 hours in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what formulas to use
to make this happen automatically upon entering the hours worked.

Te Min/Max formula that we repleid with covers the initail post
ie show o/t hours given a total working time.

with "In, Break Taken, Out, In, Break Taken, Out"
in say cols A-F
Enter data in time format, ie 9:15 0:10 13:00 14:05 0:10 17:30

you'll need a formula in G2 to calculate the work time
Then in H2, I2 put the min and max formula

Something like
G2 = (C2-A2)+(F2-D2)
will give you the total whihc will show in HH:MM format
It will actually be stored in Excel as a fraction of a day (24 hours)

So H2 = Min(G2*24,8)
I2 = Max(G2*24-8,0)

This will show Hours, O/T hours in number form

Change these to H2 = Min(G2,8/24)
and I2 = Max(G2-8/24,0)
Now the Hours worked at Normal and O/T will show as HH:MM

Depends what you actually want and what youare going to do with them

If you are going to calculate a pay for those hours, you may find it
easier to have the number form

Steve

On Tue, 22 Aug 2006 00:31:47 +0100, KimC
wrote:

I don't have a formula at all currently. I have six fields, in the
following
order:
In, Break Taken, Out, In, Break Taken, Out
I need to somehow exclude the "Break Taken" columns, as they are required
and paid ten-minute breaks. So I appreciate the help so far very much,
but
can you also assist me in the rest of the formula, or is what you
provided so
far the only formula I need?

"Elkar" wrote:

The MAX and MIN functions should do what you need. Look them up in
help for
more info.

For Straight Time:

=MIN(yoursum,8)

For Overtime:

=MAX(yoursum-8,0)

(replace yoursum with whatever formula you're currently using)

HTH,
Elkar


"KimC" wrote:

I have set up a time sheet for employees to fill out on the computer.
I need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything
beyond
that to go in the OVERTIME column. So in other words, if I work 10
hours in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what
formulas to use
to make this happen automatically upon entering the hours worked.

Maybe Chip(or is it Chuck?) Pearson can help with your problem.

See his site for timesheets calculations.

http://www.cpearson.com/excel/overtime.htm


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 16:31:47 -0700, KimC wrote:

I don't have a formula at all currently. I have six fields, in the following
order:
In, Break Taken, Out, In, Break Taken, Out
I need to somehow exclude the "Break Taken" columns, as they are required
and paid ten-minute breaks. So I appreciate the help so far very much, but
can you also assist me in the rest of the formula, or is what you provided so
far the only formula I need?

"Elkar" wrote:

The MAX and MIN functions should do what you need. Look them up in help for
more info.

For Straight Time:

=MIN(yoursum,8)

For Overtime:

=MAX(yoursum-8,0)

(replace yoursum with whatever formula you're currently using)

HTH,
Elkar


"KimC" wrote:

I have set up a time sheet for employees to fill out on the computer. I need
one column (STRAIGHT TIME) to total up to 8 hours ONLY, and anything beyond
that to go in the OVERTIME column. So in other words, if I work 10 hours in a
day, I need Excel to automatically record 8 hours in the straight time
column, and 2 hours in the overtime column. I can't find what formulas to use
to make this happen automatically upon entering the hours worked.

This formula add a1 and b1. if sum value greater than 8 hours it shows
8 hours. other wise it shows total value of added cell.

0.3333334 is the numeric value for 08:00

=IF(SUM(A1:B1)0.3333334,0.3333334,SUM(A1:B1))

This formula displays value greater than 8:00 munus 8:00

=IF(SUM(A1:B1)<0.3333334,"",SUM(A1:B1)-0.3333334)


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=573983

?? interesting use of SUM.. when + will do
together with 0.333whatever why not use (8/24)
which both explains what you are doing (selfdocumentation) and is accurate

On Tue, 22 Aug 2006 02:53:11 +0100, mudraker
wrote:


This formula add a1 and b1. if sum value greater than 8 hours it shows
8 hours. other wise it shows total value of added cell.

0.3333334 is the numeric value for 08:00

=IF(SUM(A1:B1)0.3333334,0.3333334,SUM(A1:B1))

This formula displays value greater than 8:00 munus 8:00

=IF(SUM(A1:B1)<0.3333334,"",SUM(A1:B1)-0.3333334)





--
Steve (3)

Thanks, everyone, for trying to help. I am not familiar enough with Excel for
any of this to make any sense to me -- I'm a beginner and have not worked
with Excel formulas AT ALL. So... I'm overwhelmed here. I wasn't expecting it
to be so complicated. I don't think I'm conveying myself correctly, either,
which may be part of the problem (for example -- the breaks have to be shown
as taken -- 10 minutes each in the morning and afternoon, but are NOT
subtracted from pay; they are PAID breaks...). The website I was directed to
displays a time sheet that looks nothing like mine, nor is it set up like
mine at all. So... I'm giong to have to try and find someone who can show me
in person what I need to do. Thanks anyway... If anyone wants to take a look
at the actual time sheet for me I could send it as an attachment. Then if the
first couple of lines could be formulated for me, I could copy the rest.
Otherwise, I'm at a loss.

"SteveW" wrote:

?? interesting use of SUM.. when + will do
together with 0.333whatever why not use (8/24)
which both explains what you are doing (selfdocumentation) and is accurate

On Tue, 22 Aug 2006 02:53:11 +0100, mudraker
wrote:


This formula add a1 and b1. if sum value greater than 8 hours it shows
8 hours. other wise it shows total value of added cell.

0.3333334 is the numeric value for 08:00

=IF(SUM(A1:B1)0.3333334,0.3333334,SUM(A1:B1))

This formula displays value greater than 8:00 munus 8:00

=IF(SUM(A1:B1)<0.3333334,"",SUM(A1:B1)-0.3333334)





--
Steve (3)

Kim if you give me an E-mail address I have a time sheet that does what you
are after based on a 40 hour work week not an 8 hour day. It also tracks
sick, vacation, and holiday times. At the bottom it recaps yearly numbers.
contact me at
.
Lou

"KimC" wrote:

Thanks, everyone, for trying to help. I am not familiar enough with Excel for
any of this to make any sense to me -- I'm a beginner and have not worked
with Excel formulas AT ALL. So... I'm overwhelmed here. I wasn't expecting it
to be so complicated. I don't think I'm conveying myself correctly, either,
which may be part of the problem (for example -- the breaks have to be shown
as taken -- 10 minutes each in the morning and afternoon, but are NOT
subtracted from pay; they are PAID breaks...). The website I was directed to
displays a time sheet that looks nothing like mine, nor is it set up like
mine at all. So... I'm giong to have to try and find someone who can show me
in person what I need to do. Thanks anyway... If anyone wants to take a look
at the actual time sheet for me I could send it as an attachment. Then if the
first couple of lines could be formulated for me, I could copy the rest.
Otherwise, I'm at a loss.

"SteveW" wrote:

?? interesting use of SUM.. when + will do
together with 0.333whatever why not use (8/24)
which both explains what you are doing (selfdocumentation) and is accurate

On Tue, 22 Aug 2006 02:53:11 +0100, mudraker
wrote:


This formula add a1 and b1. if sum value greater than 8 hours it shows
8 hours. other wise it shows total value of added cell.

0.3333334 is the numeric value for 08:00

=IF(SUM(A1:B1)0.3333334,0.3333334,SUM(A1:B1))

This formula displays value greater than 8:00 munus 8:00

=IF(SUM(A1:B1)<0.3333334,"",SUM(A1:B1)-0.3333334)





--
Steve (3)