Alternate from timecode to number to timecode

[workindan]

I'm importing data from Avaya CMS Call Center into excel...I run into an issue when I'm bringing in these kinds of numbers:

Total time:
8:46:53
:30:11
:18:37
etc...

When they come into excel, I get these values:
31613
1811
1117
etc...

If I format the column into any variation of (hh:mm:ss) or (h:mm:ss, mm:ss) it just outputs: 00:00:00. I've tried the timevalue, text/format functions to get it to work, and I've tried some arithmetic by dividing/multiplying 60 and 24 to see if I could find a multiplier that would produce time value of hours and minutes...but no luck their either.

Any ideas on how to take these numbers and turn them back into their timecode format?

 

[schielrn]

I believe this should work:

Excel Workbook
	A	B
24	8:46:53	31613
25	:30:11	1811
26	:18:37	1117
Sheet1

Cell Formulas
Range	Formula
B24	=(("0"&A24)+0)*86400
B25	=(("0"&A25)+0)*86400
B26	=(("0"&A26)+0)*86400

Sorry I misread your problem. I will post something different in a second.

 

[schielrn]

Here you go:

Excel Workbook
	A	B	C
24	8:46:53	31613	8:46:53
25	:30:11	1811	0:30:11
26	:18:37	1117	0:18:37
Sheet1

Cell Formulas
Range	Formula
B24	=(("0"&A24)+0)*86400
B25	=(("0"&A25)+0)*86400
B26	=(("0"&A26)+0)*86400
C24	=TEXT(TIME(0,0,B24),"h:mm:ss")
C25	=TEXT(TIME(0,0,B25),"h:mm:ss")
C26	=TEXT(TIME(0,0,B26),"h:mm:ss")

Hope that helps.

 

[workindan]

Genius! I wish I'd thought of that...

Thanks schielrn!