excel number format 2

[99mel]

I paste the following data into an excel spreadsheet:

0:00:02 
0:00:02 
0:00:01 
0:00:01 

This is a duration (h:mm:ss). Is there any possible way to do a sum of these values?

 

[xlbo]

yup - say your cells are A1:A100
in A101 enter
=SUM(A1:A100)
and format as time (hh:mm:ss)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[99mel]

doesn't seem to work. I formatted the cells as 'Time' with the type as '37:30:55' from the list.

I then do a sum but it comes back with '0.00'?

 

[xlbo]

ok - your cells are probably being treated as text - you can test this by using
=ISTEXT(A1) where A1 contains 1 of your "time" figures
If this is the case, you need to use the *1 fix

enter a 1 anywher ein the sheet and copy that cell
Then select all your "times"
Goto Edit>PasteSpecial and choose "Multiply"
Your times will probably now look like decimal numbers
This is just the serial number representation of the time
Select all the cells and goto Format>Cells and choose Time
Your sum should now work

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[99mel]

I know were very close to getting this working!!

I put a '1' in a cell and copied it.. then i selected to of my time cells and clicked pasted special and select multiply.

This keeps the format of the cell as 0:00:01 although the font changes to a bigger size. If I do a ISTEXT check on it, it still comes as true. I changed the format but the istext is still true.

 

[xlbo]

weird - I cannot replicate this - even if the 1 I enter is text, it still forces the textual time cells to be re-evaluated as numbers. If you post your email address, I'll send you an email which you can reply to with the workbook - as long as you are not using a version > '97.
If you are, save the workbook as '97 version and send that instead

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Question resolved - a non text character (char 160) was residing at the end of all the time strings - therefore rendering the *1 fix ineffectual - worked around with a formula to get rid of char160 and then use the *1 fix

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde