-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" ?
-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" ?
2
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
0
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
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
Does it make any scene
"102:16:47.04"
,, what you have entered beyond the24 Hrs Clock
!! Edit the Time value otherwise this may attract Down Vote to Close the post. – Rajesh S – 2018-12-31T05:50:14.0171@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.7971Are 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