Converting standard time to seconds

[Student for life]

Hi everyone,
I have a csv file of telephone calls. One of the columns has the duration of the call. I need to convert this to seconds. Just so you know, the values in the call duration column all look like standard time -000:00:06:44- but the cells are actually formatted general. Would someone please tell me how to do this?
Thank you in advance.

 

[Michael M]

Is this EXACTLY how they look

-000:00:06:44-

instead of

-00:06:44-

 

[Michael M]

If the times are normal standard hours
Try Formatting the cells as Custom and type in

 

[Student for life]

Hi Michael,
Thank you for your response. That's correct, the values in the cells look exactly like I showed. I assume these groups stand for number of days in a year, number of hours in a day, number of minutes in an hour and number of seconds in a minute. I followed your suggestion and changed the format of one of the cells in that column as such:
Home Tab, Cell group, Format, Format Cells, Custom, in the Type Box, I typed in S and that didn't do anything. I repeated all the steps but this time I typed and still no change. I know I can use Text to Columns and use a few simple formulas to calculate the number of seconds of each call, however I think there should be a way to format this column and get the values in decimals and not having to resort to Text to Columns.

 

[Michael M]

Ok, try this then...

Excel 2007
	A	B
1	-000:00:06:44-	404
Sheet1

Cell Formulas
Range	Formula
B1	=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"-000:",""),"-","")),"")+0

 

[Scott Huish]

=MID(A1,6,8)*86400

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:145px;" /><col style="width:174px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > -000:00:06:44-</td><td style="text-align:right; ">404</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=MID(A1,6,8)*86400</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[shg]

Or =--MID(A1, 6, 8) and format as

 

[Michael M]

...darn, that's just a tad shorter than mine !!!

 

[Student for life]

Thank you Michael, Scott and shg. These are all great and I really appreciate your help. I'm sure I will be hanging around this site and post more questions and hopefully be able to help a few others along the way.