How to sum hours, mins, secs (00:00:00)?

[Dave]

Hi, does anyone know how this is possible?

Thanks

 

[Jacob Skaria]

Dave

Another way. Enter 0 in any cell (a unused cell). Copy the cell. Select the
range with the time in text Say A1:A30. Right click>PasteSpecial> select
'Add' and OK. Now format to hh:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi

Thats great! This has solved the problem, however, is there not a way of
just converting the column to numeric format instead of having to put a new
column in?

Thanks for your help

Look like the data is in text format. Insert a column to the right such as if
Col A is having the current data; and in B1 enter formula =A1*1 and copy that
down as required. Format ColB to time hh:mm:ss. and then do the sum with
[h]:mm:ss


If this post helps click Yes
---------------
Jacob Skaria

Hi, This doesnt seem to work. There seems to be a problem with the formatting
of the cells that I am trying to sum. They display the duration as 0:00:10 if
I click into them indivdually they change to 00:00:00 and excel is able to
sum them however, this is not a solution as there are over a thousand.

I have tried formatiing the entire column but this does not work. There is
nothing to say that this is an error with the formatting of the cell and to
convert it.

What is the solution to this?

Thanks

:

Dave , do the normal sum itself; but format the total cell to [h]:mm:ss

Right click>Format Cells>Custom

If this post helps click Yes
---------------
Jacob Skaria


:

Hi, does anyone know how this is possible?

Thanks

 

[Dave]

Thanks Jacob.

This again is another great solution. I am surprised there is not a simple
function in excel to convert a column of text in to numbers as in this
instance?

Regards
David

Dave

Another way. Enter 0 in any cell (a unused cell). Copy the cell. Select the
range with the time in text Say A1:A30. Right click>PasteSpecial> select
'Add' and OK. Now format to hh:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi

Thats great! This has solved the problem, however, is there not a way of
just converting the column to numeric format instead of having to put a new
column in?

Thanks for your help

Look like the data is in text format. Insert a column to the right such as if
Col A is having the current data; and in B1 enter formula =A1*1 and copy that
down as required. Format ColB to time hh:mm:ss. and then do the sum with
[h]:mm:ss


If this post helps click Yes
---------------
Jacob Skaria


:

Hi, This doesnt seem to work. There seems to be a problem with the formatting
of the cells that I am trying to sum. They display the duration as 0:00:10 if
I click into them indivdually they change to 00:00:00 and excel is able to
sum them however, this is not a solution as there are over a thousand.

I have tried formatiing the entire column but this does not work. There is
nothing to say that this is an error with the formatting of the cell and to
convert it.

What is the solution to this?

Thanks

:

Dave , do the normal sum itself; but format the total cell to [h]:mm:ss

Right click>Format Cells>Custom

If this post helps click Yes
---------------
Jacob Skaria


:

Hi, does anyone know how this is possible?

Thanks

 

[Jon Peltier]

If the data came in as text, the column may be formatted as text. You should
change the number format to a time format, then convert to numeric values.

You actually don't need to enter zero into a cell (and delete it later).
Just copy a blank cell, then do paste Special - Add.

Another way, for a time value, is to search for : and replace by : -- it's
the same character (colon), but the replace causes Excel to evaluate each
cell, and if possible convert to a numeric value.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

## Don't miss it!
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

Dave

Another way. Enter 0 in any cell (a unused cell). Copy the cell. Select
the
range with the time in text Say A1:A30. Right click>PasteSpecial> select
'Add' and OK. Now format to hh:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi

Thats great! This has solved the problem, however, is there not a way of
just converting the column to numeric format instead of having to put a
new
column in?

Thanks for your help

Look like the data is in text format. Insert a column to the right such
as if
Col A is having the current data; and in B1 enter formula =A1*1 and
copy that
down as required. Format ColB to time hh:mm:ss. and then do the sum
with
[h]:mm:ss


If this post helps click Yes
---------------
Jacob Skaria


:

Hi, This doesnt seem to work. There seems to be a problem with the
formatting
of the cells that I am trying to sum. They display the duration as
0:00:10 if
I click into them indivdually they change to 00:00:00 and excel is
able to
sum them however, this is not a solution as there are over a
thousand.

I have tried formatiing the entire column but this does not work.
There is
nothing to say that this is an error with the formatting of the cell
and to
convert it.

What is the solution to this?

Thanks

:

Dave , do the normal sum itself; but format the total cell to
[h]:mm:ss

Right click>Format Cells>Custom

If this post helps click Yes
---------------
Jacob Skaria


:

Hi, does anyone know how this is possible?

Thanks

 

[Dave]

Thanks guys for your help.

These solutions are great with the last being the easiest.

If the data came in as text, the column may be formatted as text. You should
change the number format to a time format, then convert to numeric values.

You actually don't need to enter zero into a cell (and delete it later).
Just copy a blank cell, then do paste Special - Add.

Another way, for a time value, is to search for : and replace by : -- it's
the same character (colon), but the replace causes Excel to evaluate each
cell, and if possible convert to a numeric value.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

## Don't miss it!
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

Dave

Another way. Enter 0 in any cell (a unused cell). Copy the cell. Select
the
range with the time in text Say A1:A30. Right click>PasteSpecial> select
'Add' and OK. Now format to hh:mm:ss

If this post helps click Yes
---------------
Jacob Skaria

Hi

Thats great! This has solved the problem, however, is there not a way of
just converting the column to numeric format instead of having to put a
new
column in?

Thanks for your help

:

Look like the data is in text format. Insert a column to the right such
as if
Col A is having the current data; and in B1 enter formula =A1*1 and
copy that
down as required. Format ColB to time hh:mm:ss. and then do the sum
with
[h]:mm:ss


If this post helps click Yes
---------------
Jacob Skaria


:

Hi, This doesnt seem to work. There seems to be a problem with the
formatting
of the cells that I am trying to sum. They display the duration as
0:00:10 if
I click into them indivdually they change to 00:00:00 and excel is
able to
sum them however, this is not a solution as there are over a
thousand.

I have tried formatiing the entire column but this does not work.
There is
nothing to say that this is an error with the formatting of the cell
and to
convert it.

What is the solution to this?

Thanks

:

Dave , do the normal sum itself; but format the total cell to
[h]:mm:ss

Right click>Format Cells>Custom

If this post helps click Yes
---------------
Jacob Skaria


:

Hi, does anyone know how this is possible?

Thanks