Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
Hi, does anyone know how this is possible?
Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
Dave , do the normal sum itself; but format the total cell to [h]:mm:ss
Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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 "Jacob Skaria" wrote: 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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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 clickPasteSpecial select 'Add' and OK. Now format to hh:mm:ss If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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 "Jacob Skaria" wrote: 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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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 "Jacob Skaria" wrote: 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 clickPasteSpecial select 'Add' and OK. Now format to hh:mm:ss If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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 "Jacob Skaria" wrote: 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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
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...00906ACNJ.html _______ "Jacob Skaria" wrote in message ... 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 clickPasteSpecial select 'Add' and OK. Now format to hh:mm:ss If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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 "Jacob Skaria" wrote: 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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum hours, mins, secs (00:00:00)?
Thanks guys for your help.
These solutions are great with the last being the easiest. "Jon Peltier" wrote: 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...00906ACNJ.html _______ "Jacob Skaria" wrote in message ... 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 clickPasteSpecial select 'Add' and OK. Now format to hh:mm:ss If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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 "Jacob Skaria" wrote: 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 "Dave" wrote: 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 "Jacob Skaria" wrote: Dave , do the normal sum itself; but format the total cell to [h]:mm:ss Right clickFormat CellsCustom If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi, does anyone know how this is possible? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Cells for Mins / Secs | Excel Worksheet Functions | |||
how can i convert a value quoted in hrs,mins,secs to just mins | Excel Worksheet Functions | |||
Importing Mins:Secs | Excel Discussion (Misc queries) | |||
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? | Excel Discussion (Misc queries) | |||
Convert mins:secs to seconds only | Excel Worksheet Functions |