Found Mistake can't correct.

[Guest]

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.

 

[Chip Pearson]

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

 

[Aladin Akyurek]

=MIN(40,TotalHoursCalculation)

=MAX(0,TotalHoursCalculation-40)

 

[Ken Wright]

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

 

[JE McGimpsey]

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

 

[Trevor Shuttleworth]

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

 

[Guest]

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]

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