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
garyrobbinsAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Use this formula in A1...

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

Open in new window


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
 
Saurabh Singh TeotiaCommented:
Can you help me understand..if the value is given as..

001: 03: 10: 58

How do you want to see it?
0
 
garyrobbinsAuthor 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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Saurabh Singh TeotiaCommented:
Can you help me understand how 01:18:57:36 = 1.79 ??

What is the calculations behind it?
0
 
garyrobbinsAuthor 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
 
dsackerContract ERP Admin/ConsultantCommented:
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.

Solution using formulas
0
 
Rob HensonFinance 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
 
Rob HensonFinance 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
 
garyrobbinsAuthor Commented:
Yes with spaces, Thank you very much
0
 
Saurabh Singh TeotiaCommented:
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
 
Rob HensonFinance AnalystCommented:
Saurabh, no need for the SUBSTITUTE function, even with spaces the TIMEVALUE still worked.
0
 
Saurabh Singh TeotiaCommented:
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.

All Courses

From novice to tech pro — start learning today.