Formula to Convert Time text to Excel and add up column

[Patish]

Hi
I've got a column of times in h:m:s:ms. (ms= microseconds)
The times are text format. I need to convert them into time format and get the total time at the bottom of the column.
Thanks
Patish

[Richard Buttrey]

Can we just make sure that we both understand the term microseconds.

There are 1,000,000 microseconds in a second.
Is that indeed what your numbers like the :20, meaning 20 microseconds is at the end of A2?

[Bo_Ry]

Please try at B2
=--SUBSTITUTE(A2,":",".",3)

or Total time
=SUMPRODUCT(--SUBSTITUTE(A2:A71,":",".",3))

custom format [h]:mm:ss.00

[Patish]

Hi Richard
Sorry "ms" is actually millisecond. Does that make a difference?
Thanks

[Patish]

Hi Bo_Ry
Just replied to Richard that the ms is actually milliseconds. Does that make any difference to the formula?
Tx
Patish

[Richard Buttrey]

The other Q. I had, which may be redundant if Bo_Ry's answer give you what you want, but what do you expect in the millisecond part of the string.

Suppose the total ended in say 500 ms would you want to see

01:01:01:500

or

01:01:01.5

[Patish]

Hi Richard
If it's 500 then :500
Tx
Patish

[vemulasandeep]

Hi
I have a column over 10,000 records of time in excel some are sorted as original time format of excel and some are mentioned as text and some as numbers ex(10.0, 3.30 etc.,)
in this situation How can i convert the time non standard time formats to a standard time format with out changing the time value.

I tried the following formulas but not getting correct results;
1. =TEXT(LEFT(A1,2)&RIGHT(A1,2)/24,"h:mm am/pm")
2. =IF(ISNUMBER(A1),A1,TIMEVALUE((A1)))

please suggest formula to resolve this

Thanks and Regards
Sandeep Vemula

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread