• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Excel converting time from text to time

Hello,

I have a column of information showing 00:03:29 for example but in text so I can get an average of all the times in the column.  I tried data format convert to time and even text to columns, but it does not work.  

Thanks,
John
0
jverasql
Asked:
jverasql
1 Solution
 
AlanConsultantCommented:
Hi,

I am guessing that means 0 hrs, 3 mins, and 29 secs?

If so, and you have that string ("00:03:29") in cell A1, then this should convert it to a timevalue:

=TIME(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))

The actual 'value' will be 0.00241898148148148 which is the portion of a day that is represented by that amount of time.

To format the result cell to show 00:03:29 (but keep the value as a timevalue), use this format:

hh:mm:ss


HTH,

Alan.
0
 
krishnakrkcCommented:
Try

=AVERAGE(--A1:A10)

Its an array formula. Confirmed with CTRL+SHIFT+ENTER

and convert the cell into time format.

Kris
0
 
Rob HensonFinance AnalystCommented:
Or just convert using the TIMEVALUE function:

=TIMEVALUE(A1)

Result will be a decimal, then format as Time.

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now