Problem with formula =MAX(A1-40,0)

[Lee]

I was using the formula =MAX(A1-40,0) to seperate overtime hours from total
hours worked on a time sheet. The cell was formatted "general". Now I need to
format the cells "[h]:mm" but the formula no longer works with that cell
formatting. Is there a fix?
--
Lee Davenport

[Gary''s Student]

Try:

=MAX(A1-1.66666667,0)

As re-formatted 24 hrs = 1 day
so
40 hrs = 1.6666667 days
--
Gary's Student


"Lee" wrote:

> I was using the formula =MAX(A1-40,0) to seperate overtime hours from total
> hours worked on a time sheet. The cell was formatted "general". Now I need to
> format the cells "[h]:mm" but the formula no longer works with that cell
> formatting. Is there a fix?
> --
> Lee Davenport

[David Benson]

Lee,

You have run afoul of Microsoft's convention for displaying times and dates.

When you tell Excel that a cell contains a date or time, then Excel assumes
that the integer (whole-number) part of the value of the cell represents the
day (where Day 1 is Jan. 1, 1900). The fractional part of the value of the
cell represents the time, expressed as a fraction of a 24-hour day.

When you converted a cell containing the value "41" to date and time, Excel
assumed that the value meant midnight on February 10, 1900, which is 41 days
after Jan. 1, 1900. If you converted a cell containing the formula you gave
below, then Excel interpreted as midnight of some other day, the exact day
being determined by the number of overtime hours.

Change your formula to this:

=MAX((A1-40)/24,0)

Note that Cell A1 will have to contain a time in hours and fractions of an
hour. For example, if someone worked 41-1/2 hours, you would have to enter
"41.5" in Cell A1.

Hope this helps.


-- David Benson


"Lee" <[email protected]> wrote in message
news:[email protected]...
>I was using the formula =MAX(A1-40,0) to seperate overtime hours from total
> hours worked on a time sheet. The cell was formatted "general". Now I need
> to
> format the cells "[h]:mm" but the formula no longer works with that cell
> formatting. Is there a fix?
> --
> Lee Davenport