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" <(E-Mail Removed)> wrote in message
news:73D4F608-D73B-401B-A6E6-(E-Mail Removed)...
>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
>

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