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