Converting time string text to hours and minutes in Excel

-2

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" ?

lakesh

Posted 2018-12-31T02:33:11.427

Reputation: 352

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 S – 2018-12-31T05:50:14.017

1@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds. – jonsca – 2018-12-31T07:51:02.490

@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!! – Rajesh S – 2018-12-31T08:29:28.640

@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 – 2018-12-31T08:40:48.600

Converting it from a string back to a date yields 1/4/1900 6:16:47 AM which is a perfectly valid value. Whether that is applicable to any reasonable situation is immaterial. – jonsca – 2018-12-31T08:43:55.797

1Are your values stored as strings or Excel time values formatted to appear in that format? – fixer1234 – 2019-01-06T03:16:10.397

@RajeshS Relax! Odds are, a viewer wouldn’t impulsively downvote, rather ask for more detail in the comments. – juniorRubyist – 2019-01-06T03:44:00.433

Answers

You can just format it:

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

Ron Rosenfeld

Posted 2018-12-31T02:33:11.427

Reputation: 3 333

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

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

jonsca

Posted 2018-12-31T02:33:11.427

Reputation: 3 889

I'm assuming your string is just 102:16:47.04 without the quotes around it. – jonsca – 2018-12-31T04:13:19.603

Your Formula returns "102:16 last " is missing it should "102:16" !! – Rajesh S – 2018-12-31T08:43:26.207

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 – 2018-12-31T08:45:29.333

,, 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 S – 2018-12-31T08:58:02.577