Converting time string text to hours and minutes in Excel

Question

I have a time string in a text-formatted cell that looks like this: "102:16:47.04". How can I convert it to time string like this: "102:16" ?

Does it make any scene "102:16:47.04" ,, what you have entered beyond the 24 Hrs Clock!! Edit the Time value otherwise this may attract Down Vote to Close the post. — Rajesh Sinha, Commented Dec 31, 2018 at 5:50
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds. — jonsca, Commented Dec 31, 2018 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!! — Rajesh Sinha, Commented Dec 31, 2018 at 8:29
@RajeshS Why does it matter, really? It seems like he just wants to do some basic string processing. The example could have been anything. I was curious as to whether there was a need to parse it back into a date and time to truncate it, but even then, it would still work. — jonsca, Commented Dec 31, 2018 at 8:40
Are your values stored as strings or Excel time values formatted to appear in that format? — fixer1234, Commented Jan 6, 2019 at 3:16

Ron Rosenfeld · Accepted Answer · 2019-01-06 02:19:35Z

2

You can just format it:

=TEXT(myTimeString,"[hh]:mm")

answered Jan 6, 2019 at 2:19

Ron Rosenfeld

8,9313 gold badges14 silver badges17 bronze badges

Add a comment |

Giacomo1968 · Accepted Answer · 2018-12-31 04:16:02Z

0

If your string is in A1, you can get the substring via this formula:

=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)

Giacomo1968

56.1k23 gold badges167 silver badges214 bronze badges

answered Dec 31, 2018 at 4:12

jonsca

4,06215 gold badges36 silver badges47 bronze badges

I'm assuming your string is just 102:16:47.04 without the quotes around it.
– jonsca
Commented Dec 31, 2018 at 4:13
Your Formula returns "102:16 last ` " ` is missing it should "102:16" !!
– Rajesh Sinha
Commented Dec 31, 2018 at 8:43
1

@RajeshS As I wrote in my comment immediately above yours, I'm assuming the OP is only processing the content of the string and has delineated it with the quotes. Please tone it down a bit.
– jonsca
Commented Dec 31, 2018 at 8:45
,, since the Time is within the Quotes and OP wants to return "102:16" then only LEFT Function can do it =LEFT(A1,7)&"""" !!
– Rajesh Sinha
Commented Dec 31, 2018 at 8:58

Add a comment |

2 Answers 2