Adding time after calculation

[mrrherrera]

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.

 

[Bob Phillips]

Format it as [h]:mm

 

[mrrherrera]

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.

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

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]

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.

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.

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

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.

 

[mrrherrera]

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!

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.

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.

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.

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!

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.

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.

:

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.

 

[mrrherrera]

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.

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.

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!

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.

:

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]

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?

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.

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.

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!

:

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.

:

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.

 

[mrrherrera]

I wrote my answers next to your questions.

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.

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.

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!

:

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.

:

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]

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.

I wrote my answers next to your questions.

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.

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.

:

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!

:

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.

:

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.