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.

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.

Format it as [h]:mm

--
__________________________________
HTH

Bob

"mrrherrera" wrote in message
...
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.

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.

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

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

You won't see 1.1 if it's formatted as [hh]:mm
Check your sheet again.
--
David Biddulph

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

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

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

What formula did you use in column C?

What formula did you use to get the total in column C?

What was the numberformat for those cells with the formulas?

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

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

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

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