And you don't want to tell us what format you've used in column C?

You really are making it painfully difficult for us to help you.

There is also some problem in that you are telling us that you have used
=Sum(H9:H10) to get the total in column C, but it isn't clear where column H
comes from, as you told us "I have three column involved, a, b, & c" and
that "Column C then calculates the difference between Columns B & A".
Another inconsistency is that you said "Each column has 30 rows" and that
"Column C, Row 31 calculates the total the sum of column C generating the
total overtime", so it isn't clear why you then say that the total in column
C comes from "=Sum(H9:H10)" which seems to be adding 2 rows in column H
rather than 30 rows in column C. If you are adding the wrong number of rows
in the wrong column, you would be extremely lucky if you got the right
answer.
You got us further confused when you said that the formula you used in
column C was "=TEXT(D9-C9,"[h]: mm")" [which would have a circular reference
with respect to C9] although you said that the start and finish times were
in columns A and B, not columns C and D.

Would you like to start again and tell what you are really doing, and what
the formulae and formats were that gave you the result of 1.1?
--
David Biddulph

mrrherrera wrote:
I wrote my answers next to your questions.
"Dave Peterson" wrote:

What formula did you use in column C? =TEXT(D9-C9,"[h]: mm")

What formula did you use to get the total in column C? =Sum(H9:H10).

What was the numberformat for those cells with the formulas? After
reading the replies to my post I took off the TEXT from the formula
and set the custom format to [hh]:mm (Cols. a & b). This is when I
received the answer of 1.1. The correct answer for the total should
be 28 hours of OT.

mrrherrera wrote:

To clarify, I have three column involved, a, b, & c. Each column
has 30 rows. Column b has the ending time and column a has the
starting time, e.g. overtime shift starts at 5:00 PM (column A) and
ends at 7:00 PM (Column B). Column C then calculates the difference
between Columns B & A. Column C, Row 31 calculates the total the
sum of column C generating the total overtime.

"Dave Peterson" wrote:

I don't understand.

If the number is formatted as [hh]:mm, how can the answer be 28?

I'd understand 28:00, 00:28 or something like that.

If you share the formula and the value in each of the cells that
that formulas uses, it'll be easier to help.

mrrherrera wrote:

I changed the custom format to [hh]:mm. I deleted TEXT for each
cell. The formula is just the subtraction of the two cells,
=d9-c9. When I added the column down, it gave me an answer of
1.1, when the answer should be 28. HELP!

"Dave Peterson" wrote:

Don't use the =text() worksheet function.

Just do regular subtraction, but use a custom format:
Format|cells|number tab|Custom
[hh]:mm
or whatever you want.

If you use =text() then the cells contain text/strings. And
=sum() will ignore them.

mrrherrera wrote:

I tried what you suggested. It did not work. Here is the
formula =TEXT(D9-C9,"[h]: mm") for the first cell. The second
cell's is =TEXT(D10-C10,"[h]: mm'). I then did =Sum(H9:H10).
It gave me a blank cell.

"David Biddulph" wrote:

If your hh:mm problem is when you exceed 24 hours, try [hh]:mm
--
David Biddulph

mrrherrera wrote:
I have a spreadsheet where it calculates the time between two
hours each day. At the end of the month, I want to be able to
add up the calculated times. I've tried =Sum(a1:a31), but it
does not work. Each column is formatted for time as HH:MM. I
tried changing it to HH:MM:SS that didn't work either. Thanks
in advance for your help.




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson