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" ?
microsoft-excel date-time
edited Jan 8 at 17:25
Debra
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
|
show 2 more comments
-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" ?
microsoft-excel date-time
edited Jan 8 at 17:25
Debra
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
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
Dec 31 '18 at 5:50
1
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:40
1
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3:16
|
show 2 more comments
-2
-2
-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" ?
microsoft-excel date-time
edited Jan 8 at 17:25
Debra
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
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" ?
microsoft-excel date-time
microsoft-excel date-time
edited Jan 8 at 17:25
Debra
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
edited Jan 8 at 17:25
Debra
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
edited Jan 8 at 17:25
Debra
3,85011021
edited Jan 8 at 17:25
Debra
3,85011021
edited Jan 8 at 17:25
Debra
3,85011021
3,85011021
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
asked Dec 31 '18 at 2:33
lakeshlakesh
2073715
2073715
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
Dec 31 '18 at 5:50
1
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:40
1
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3:16
|
show 2 more comments
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
Dec 31 '18 at 5:50
1
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:40
1
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3: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 S
Dec 31 '18 at 5:50
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
Dec 31 '18 at 5:50
1
1
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 at 8:29
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:40
@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
Dec 31 '18 at 8:40
1
1
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3:16
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3:16
|
show 2 more comments
2 Answers
2
active
oldest
votes
2
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
add a comment |
0
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
I'm assuming your string is just102:16:47.04without the quotes around it.
– jonsca
Dec 31 '18 at 4:13
Your Formula returns"102:16last ` " ` is missing it should"102:16"!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:45
,, since the Time is within the Quotes and OP wants to return"102:16"then onlyLEFTFunction can do it=LEFT(A1,7)&""""!!
– Rajesh S
Dec 31 '18 at 8:58
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389128%2fconverting-time-string-text-to-hours-and-minutes-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
add a comment |
2
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
add a comment |
2
2
2
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
You can just format it:
=TEXT(myTimeString,"[hh]:mm")
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
answered Jan 6 at 2:19
Ron RosenfeldRon Rosenfeld
1,9972611
1,9972611
add a comment |
add a comment |
0
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
I'm assuming your string is just102:16:47.04without the quotes around it.
– jonsca
Dec 31 '18 at 4:13
Your Formula returns"102:16last ` " ` is missing it should"102:16"!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:45
,, since the Time is within the Quotes and OP wants to return"102:16"then onlyLEFTFunction can do it=LEFT(A1,7)&""""!!
– Rajesh S
Dec 31 '18 at 8:58
add a comment |
0
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
I'm assuming your string is just102:16:47.04without the quotes around it.
– jonsca
Dec 31 '18 at 4:13
Your Formula returns"102:16last ` " ` is missing it should"102:16"!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:45
,, since the Time is within the Quotes and OP wants to return"102:16"then onlyLEFTFunction can do it=LEFT(A1,7)&""""!!
– Rajesh S
Dec 31 '18 at 8:58
add a comment |
0
0
0
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
If your string is in A1, you can get the substring via this formula:
=LEFT(A1,(FIND(":",A1,FIND(":",A1)+1))-1)
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
edited Dec 31 '18 at 4:16
JakeGould
31.3k1096138
31.3k1096138
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
answered Dec 31 '18 at 4:12
jonscajonsca
2,993112539
2,993112539
I'm assuming your string is just102:16:47.04without the quotes around it.
– jonsca
Dec 31 '18 at 4:13
Your Formula returns"102:16last ` " ` is missing it should"102:16"!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:45
,, since the Time is within the Quotes and OP wants to return"102:16"then onlyLEFTFunction can do it=LEFT(A1,7)&""""!!
– Rajesh S
Dec 31 '18 at 8:58
add a comment |
I'm assuming your string is just102:16:47.04without the quotes around it.
– jonsca
Dec 31 '18 at 4:13
Your Formula returns"102:16last ` " ` is missing it should"102:16"!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:45
,, since the Time is within the Quotes and OP wants to return"102:16"then onlyLEFTFunction can do it=LEFT(A1,7)&""""!!
– Rajesh S
Dec 31 '18 at 8:58
I'm assuming your string is just
102:16:47.04 without the quotes around it.– jonsca
Dec 31 '18 at 4:13
I'm assuming your string is just
102:16:47.04 without the quotes around it.– jonsca
Dec 31 '18 at 4:13
Your Formula returns
"102:16 last ` " ` is missing it should "102:16" !!– Rajesh S
Dec 31 '18 at 8:43
Your Formula returns
"102:16 last ` " ` is missing it should "102:16" !!– Rajesh S
Dec 31 '18 at 8:43
1
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
Dec 31 '18 at 8:45
@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
Dec 31 '18 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 S
Dec 31 '18 at 8:58
,, 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
Dec 31 '18 at 8:58
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389128%2fconverting-time-string-text-to-hours-and-minutes-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Dec 31 '18 at 5:50
1
@RajeshS As a time lapse, it makes perfect sense: 102 hours, 16 minutes, 47 seconds, and 40 milliseconds.
– jonsca
Dec 31 '18 at 7:51
@jonsca,, but no where OP has mentioned about the Time Laps,, and in what regard!!
– Rajesh S
Dec 31 '18 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
Dec 31 '18 at 8:40
1
Are your values stored as strings or Excel time values formatted to appear in that format?
– fixer1234
Jan 6 at 3:16