Problems converting text to time.. and calculating
Posted by Jennifer on November 22, 2001 9:20 AM
I have a spreadsheet of several hundred FROM and TO times. They were converted from a DBIV spreadsheet, and all the times are listed with an apostrophe before them. They are also listed in "normal" (not decimal) format of hhmmss
'102034 '113426
what i need to do is strip the seconds off (i have no problems there), and then calculate the elapsed time between columns, in hours and minutes, and the do an average of the elapsed time.
what i'm having the most problem with is converting the stripped text into hour/minute format to do the calculations. Do i even need to do this part?
Any easy way to do this, and FAST? I need to figure out the calculations by Monday morning.
Posted by Juan Pablo on November 22, 2001 9:25 AM
Use the TIME function to "convert" this values to time... something like
=TIME(MID(A1,2,2),MID(A1,4,2),RIGHT(A1),2)
This will convert '102034 to 10:20:34
Juan Pablo
Posted by Juan Pablo on November 22, 2001 9:30 AM
Or...
... if Excel "recognizes" the ' as it usually does, then try with:
=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1),2)
If you don't care about the seconds, just change this to
=TIME(LEFT(A1,2),MID(A1,3,2),0)
Juan Pablo
Posted by Jennifer on November 22, 2001 9:44 AM
cool, that part worked, and i got the calculations of overall time, but now i'm having problems finding an average time. when i try avg(h2:h3), it gives me a #NAME? error. can times be averaged?
Posted by Jennifer on November 22, 2001 9:56 AM
nevermind. i figured it out.
thanks!
Posted by Aladin Akyurek on November 22, 2001 9:57 AM
Yes, with AVERAGE. (NT)
