Excel -- Time Functions 3

[risk99]

Hi, I've downloaded some data from the Web, and it has a column for time, but it's in TEXT format, so the time reads like this : 102530 (the hour is 10, minute is 25, second is 30). When I tried to change the cell format to "Time" , everything goes to " 00:00:00 ". I've tried the following functions from Excel, but it didn't work:

TIME, HOUR, MINUTE, SECOND

Please help!!!

 

[SkipVought]

Hi,

Time values in Excel are fractions of a day

So you must convert the string to time

=TIME(LEFT(E1,2), MID(E1,3,2), RIGHT(E1,2))

where E1 is your string



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[risk99]

Thank you very much Skip!!!

 

[gapiesco]

Skip, anyone.

Will what you discussed here help me format a cell so that I can type: 102530 and have the cell display as 10:25:30. I would also need to make sure is can deal with time durations, not time clock. For example, 1235010 to represent 123:50:10 or 123 hrs, 50 mins, 10 secs.

 

[SkipVought]

use this formula AND format the cell [h]:mm:ss

=((INT(G2/10000))+((LEFT(RIGHT(G2,4),2))+((RIGHT(G2,2)))/60)/60)/24

where G2 is your time digit string such as 1235010 returning

123:50:10

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gapiesco]

Thanks. Skip.

Sorry it took so long to get back to you. Too much work activity to implement change. Now it's 2004 and I'm streamlining.