# Excel Time as Text

I dont need in macro just one time cell equation.

I have a column that has the below duration times stored as text, how do i convert them to # values that i can use in calculations

I need to find the average.

Days:Hours:Minutes:Seconds

000: 00: 30: 34
000: 00: 30: 00
000: 00: 24: 19
000: 00: 35: 43
000: 00: 39: 29
000: 01: 02: 57
001: 03: 10: 58
001: 01: 56: 23
###### Who is Participating?

Commented:
Use this formula in A1...

``````=(LEFT(A1,3)*1)+TIME(MID(A1,6,2)*1,MID(A1,10,2)*1,RIGHT(A1,2)*1)
``````

and drag this..it will do what you are looking for..

What i have assumed your data is in this format...

001: 03: 10: 58

which is that you have space after every colon sign..

Saurabh
0

Commented:
Can you help me understand..if the value is given as..

001: 03: 10: 58

How do you want to see it?
0

Author Commented:
The cell shows 001: 03: 10: 58 but it is not a time value it is a text so i cant do any calculations against it.  I want it to show the exact same info but as a Time Value.

For example  01:18:57:36 is actually 1.79 when showed as a #
0

Commented:
Can you help me understand how 01:18:57:36 = 1.79 ??

What is the calculations behind it?
0

Author Commented:
That's just the excel number that represents that time.  left of the . is days and right of the . is the faction of the day I.e. Time.

Just like a 42067.49 is the numerical value of 3/4/15 11:45:15
0

You'll need to set a column to your right as your work column, because you'll need a formula to convert those values to seconds. Here is what your cells should look like after they are done.

0

Finance AnalystCommented:
For number of days:

=LEFT(A2,3)*1

For hh:mm:ss

=TIMEVALUE(RIGHT(A2,LEN(A2)-4))

How do you want the end result?
0

Finance AnalystCommented:
For complete value in one cell:

=LEFT(A2,3)*1+TIMEVALUE(RIGHT(A2,LEN(A2)-4))

Format with Custom format "[h]:mm:ss" (without quotes) and it will show full quantity of hours without rounding back to zero after each complete set of 24 ie each whole day.

Thanks
Rob H
0

Author Commented:
Yes with spaces, Thank you very much
0

Commented:
Was looking in this.. and a small tweak what rob posted will also work for you which is this... will give you the same results as well...

=LEFT(A1,3)*1+TIMEVALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-5)," ",""))

Saurabh...
0

Finance AnalystCommented:
Saurabh, no need for the SUBSTITUTE function, even with spaces the TIMEVALUE still worked.
0

Commented:
Ahh Rob good point out...Thanks i didn't knew it..since never used this function with space...
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.