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