If you use =text(), then the values returned are text, not numbers.

Use a formula like:
=d9-c9
and give the cell a custom format of:
[h]:mm

This is the same suggestion as before. Give it a try.

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


--

Dave Peterson