In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes over
40 hours in a week. I am trying to correct this without having to start all
over again. I need to return one of two values based on the total hours for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are 40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

See www.cpearson.com/excel/overtime.htm for example formulas for
dealing with time sheets and overtime.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"dvonj" wrote in message
...
In my haste to show off what I created for my company I
overlooked a minor
detail. A major one if any one else finds it. I put together a
spreadsheet
time card that will total driver hours but overlooked if a
driver goes over
40 hours in a week. I am trying to correct this without having
to start all
over again. I need to return one of two values based on the
total hours for
the week. If Total Hours is < 40, I need to return those hours
as regular
hours, if the Total Hours is 40 I need to return 40.00 in one
cell and the
remaining hour as overtime hours in another cell. I have tried
several
different fomulas for this and can get it to work if the Total
Hours are 40
but it won't work if they are < 40. I need a quick and dirty
fix before we
put this into production this Wednesday.

Thanks.

=MIN(40,TotalHoursCalculation)

=MAX(0,TotalHoursCalculation-40)

dvonj wrote:
In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes over
40 hours in a week. I am trying to correct this without having to start all
over again. I need to return one of two values based on the total hours for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are 40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

=MIN(40,Your_formula) will return everything up to and including 40 hours

=MAX(0,Your_Formula-40) will return all hours over 40, or 0 if none.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"dvonj" wrote in message
...
In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes
over
40 hours in a week. I am trying to correct this without having to start
all
over again. I need to return one of two values based on the total hours
for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and
the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are
40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

one way:

Assume your hours are in A2:G2. Then calculate regular hours as

H2: =MIN(40,SUM(A2:G2))

and OT hours:

I2: =MAX(SUM(A2:G2)-H2,0)


For additional approaches, see:

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

and

http://www.j-walk.com/ss/excel/files/timesht.htm

In article ,
"dvonj" wrote:

In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes over
40 hours in a week. I am trying to correct this without having to start all
over again. I need to return one of two values based on the total hours for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are 40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

Are you looking for something like:

=IF(D1<=40,0,D1-40) if the total hours are in cell D1

or maybe:

=IF(D1<=40,"",D1-40)

Or have I missed something ?

Regards

Trevor


"dvonj" wrote in message
...
In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes
over
40 hours in a week. I am trying to correct this without having to start
all
over again. I need to return one of two values based on the total hours
for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and
the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are
40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

Thanks for your help. After playing around with it for a couple of hours I
was able to make your formulas fit. Appreciate your help.

"Ken Wright" wrote:

=MIN(40,Your_formula) will return everything up to and including 40 hours

=MAX(0,Your_Formula-40) will return all hours over 40, or 0 if none.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"dvonj" wrote in message
...
In my haste to show off what I created for my company I overlooked a minor
detail. A major one if any one else finds it. I put together a spreadsheet
time card that will total driver hours but overlooked if a driver goes
over
40 hours in a week. I am trying to correct this without having to start
all
over again. I need to return one of two values based on the total hours
for
the week. If Total Hours is < 40, I need to return those hours as regular
hours, if the Total Hours is 40 I need to return 40.00 in one cell and
the
remaining hour as overtime hours in another cell. I have tried several
different fomulas for this and can get it to work if the Total Hours are
40
but it won't work if they are < 40. I need a quick and dirty fix before we
put this into production this Wednesday.

Thanks.

LOL - When you get 5 answers that are virtually identical then you can feel
pretty safe that it's going to work :-)

--
Regards
Ken.......................

<snip