Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel converting time from text to time

Posted on 2015-01-07
3
Medium Priority
?
220 Views
Last Modified: 2015-01-08
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
Comment
Question by:jverasql
3 Comments
 
LVL 21

Expert Comment

by:Alan
ID: 40537131
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
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40537161
Try

=AVERAGE(--A1:A10)

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

and convert the cell into time format.

Kris
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40537656
Or just convert using the TIMEVALUE function:

=TIMEVALUE(A1)

Result will be a decimal, then format as Time.

Thanks
Rob H
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
Conducting a customer service survey used to be as straightforward as sending a template email out using checkboxes and numerical rating systems to measure satisfaction.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question