Change “XX:XX:00” to “00:XX:XX”





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















So I have this data directly copy/paste from iTunes : enter image description here



Excel have "XX:XX:00" format which is "hh:mm:ss" but as you can imagine, it is more like "mm:ss:00". Musics are not hours long !



As it's not a direct cell format problem, I couldn't find easily the answer on internet. I need sometihng to put the 2 "00" at the start (in order to have "00:mm:ss")



EDIT : It was a cell format comprehension problem.






excel







share|improve this question

























  • Assuming you already have 11:42, what would be wrong with "00:"&C2?

    – Tim Biegeleisen
    Jan 3 at 10:34











  • It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

    – mewX Max
    Jan 3 at 10:41




















1















So I have this data directly copy/paste from iTunes : enter image description here



Excel have "XX:XX:00" format which is "hh:mm:ss" but as you can imagine, it is more like "mm:ss:00". Musics are not hours long !



As it's not a direct cell format problem, I couldn't find easily the answer on internet. I need sometihng to put the 2 "00" at the start (in order to have "00:mm:ss")



EDIT : It was a cell format comprehension problem.






excel







share|improve this question

























  • Assuming you already have 11:42, what would be wrong with "00:"&C2?

    – Tim Biegeleisen
    Jan 3 at 10:34











  • It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

    – mewX Max
    Jan 3 at 10:41
















1












1








1








So I have this data directly copy/paste from iTunes : enter image description here



Excel have "XX:XX:00" format which is "hh:mm:ss" but as you can imagine, it is more like "mm:ss:00". Musics are not hours long !



As it's not a direct cell format problem, I couldn't find easily the answer on internet. I need sometihng to put the 2 "00" at the start (in order to have "00:mm:ss")



EDIT : It was a cell format comprehension problem.






excel







share|improve this question
















So I have this data directly copy/paste from iTunes : enter image description here



Excel have "XX:XX:00" format which is "hh:mm:ss" but as you can imagine, it is more like "mm:ss:00". Musics are not hours long !



As it's not a direct cell format problem, I couldn't find easily the answer on internet. I need sometihng to put the 2 "00" at the start (in order to have "00:mm:ss")



EDIT : It was a cell format comprehension problem.






excel




excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 12:19







mewX Max

















asked Jan 3 at 10:27









mewX MaxmewX Max

176




176













  • Assuming you already have 11:42, what would be wrong with "00:"&C2?

    – Tim Biegeleisen
    Jan 3 at 10:34











  • It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

    – mewX Max
    Jan 3 at 10:41





















  • Assuming you already have 11:42, what would be wrong with "00:"&C2?

    – Tim Biegeleisen
    Jan 3 at 10:34











  • It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

    – mewX Max
    Jan 3 at 10:41



















Assuming you already have 11:42, what would be wrong with "00:"&C2?

– Tim Biegeleisen
Jan 3 at 10:34





Assuming you already have 11:42, what would be wrong with "00:"&C2?

– Tim Biegeleisen
Jan 3 at 10:34













It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

– mewX Max
Jan 3 at 10:41







It is giving me "00:0,4875". It is a format problem (tought it wasn't but...) as the "Durée" column, in text format give me "0,4875"

– mewX Max
Jan 3 at 10:41














3 Answers
3






active

oldest

votes


















1














You can just divide the cell value to 60 like so



enter image description here



and then choose custom format for that cell like this



enter image description here






share|improve this answer
























  • It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

    – mewX Max
    Jan 3 at 12:15






  • 1





    @mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

    – Daut
    Jan 3 at 12:22





















1














Format the cell with the time (C1) as "General". If it retains its value, like 11:42, then convert it with =TimeValue(C1) and format the cell as Custom mm:ss



If it changes to something like 0.4875 then convert it with =C1/60 and format the result as Custom mm:ss






share|improve this answer
























  • It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

    – Daut
    Jan 3 at 12:07






  • 1





    Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

    – Variatus
    Jan 3 at 12:18



















1














This formula should work:



="00:" & MID(TEXT(A1,"h:mm:ss"),SEARCH(":",TEXT(A1,"h:mm:ss"))+1,3) & RIGHT(TEXT(A1,"h:mm:ss"),2)


The important element is to convert the time into a text string.




  • Change A1 to the first cell of Duration (Duree) & copy the formula downward

  • Then, you can copy the result and paste it as values


Edit: you can also use just the right function:



="00:"&RIGHT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-SEARCH(":",TEXT(A1,"h:mm:ss")))





share|improve this answer


























  • You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

    – mewX Max
    Jan 3 at 10:52






  • 1





    Glad it helped. I added another option to the answer because you only need the right function for the purpose.

    – Ali NasserEddine
    Jan 3 at 11:24












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54020432%2fchange-xxxx00-to-00xxxx%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














You can just divide the cell value to 60 like so



enter image description here



and then choose custom format for that cell like this



enter image description here






share|improve this answer
























  • It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

    – mewX Max
    Jan 3 at 12:15






  • 1





    @mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

    – Daut
    Jan 3 at 12:22


















1














You can just divide the cell value to 60 like so



enter image description here



and then choose custom format for that cell like this



enter image description here






share|improve this answer
























  • It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

    – mewX Max
    Jan 3 at 12:15






  • 1





    @mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

    – Daut
    Jan 3 at 12:22
















1












1








1







You can just divide the cell value to 60 like so



enter image description here



and then choose custom format for that cell like this



enter image description here






share|improve this answer













You can just divide the cell value to 60 like so



enter image description here



and then choose custom format for that cell like this



enter image description here







share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 3 at 12:02









DautDaut

1,18511024




1,18511024













  • It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

    – mewX Max
    Jan 3 at 12:15






  • 1





    @mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

    – Daut
    Jan 3 at 12:22





















  • It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

    – mewX Max
    Jan 3 at 12:15






  • 1





    @mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

    – Daut
    Jan 3 at 12:22



















It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

– mewX Max
Jan 3 at 12:15





It works well !Asuming that excel convert 1 as 24h, I understand why 0,4875 is 11:42 and why I can just divide by 60 to have minutes instead of hours.

– mewX Max
Jan 3 at 12:15




1




1





@mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

– Daut
Jan 3 at 12:22







@mewXMax Excel keeps 11:42 as 0.4875 in the cell (you see it as 11:42). 0.4875 / 60 = 0.008125 and that is the value of the other cell. When you format it as time, it shows 00:11:42

– Daut
Jan 3 at 12:22















1














Format the cell with the time (C1) as "General". If it retains its value, like 11:42, then convert it with =TimeValue(C1) and format the cell as Custom mm:ss



If it changes to something like 0.4875 then convert it with =C1/60 and format the result as Custom mm:ss






share|improve this answer
























  • It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

    – Daut
    Jan 3 at 12:07






  • 1





    Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

    – Variatus
    Jan 3 at 12:18
















1














Format the cell with the time (C1) as "General". If it retains its value, like 11:42, then convert it with =TimeValue(C1) and format the cell as Custom mm:ss



If it changes to something like 0.4875 then convert it with =C1/60 and format the result as Custom mm:ss






share|improve this answer
























  • It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

    – Daut
    Jan 3 at 12:07






  • 1





    Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

    – Variatus
    Jan 3 at 12:18














1












1








1







Format the cell with the time (C1) as "General". If it retains its value, like 11:42, then convert it with =TimeValue(C1) and format the cell as Custom mm:ss



If it changes to something like 0.4875 then convert it with =C1/60 and format the result as Custom mm:ss






share|improve this answer













Format the cell with the time (C1) as "General". If it retains its value, like 11:42, then convert it with =TimeValue(C1) and format the cell as Custom mm:ss



If it changes to something like 0.4875 then convert it with =C1/60 and format the result as Custom mm:ss







share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 3 at 10:47









VariatusVariatus

5,7351524




5,7351524













  • It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

    – Daut
    Jan 3 at 12:07






  • 1





    Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

    – Variatus
    Jan 3 at 12:18



















  • It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

    – Daut
    Jan 3 at 12:07






  • 1





    Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

    – Variatus
    Jan 3 at 12:18

















It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

– Daut
Jan 3 at 12:07





It will change to 0.4875 every time, if it changed 11:42 to 11:42:00

– Daut
Jan 3 at 12:07




1




1





Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

– Variatus
Jan 3 at 12:18





Divide 0.4875 by 60 to convert hours to minutes. Then format the cell as Time.

– Variatus
Jan 3 at 12:18











1














This formula should work:



="00:" & MID(TEXT(A1,"h:mm:ss"),SEARCH(":",TEXT(A1,"h:mm:ss"))+1,3) & RIGHT(TEXT(A1,"h:mm:ss"),2)


The important element is to convert the time into a text string.




  • Change A1 to the first cell of Duration (Duree) & copy the formula downward

  • Then, you can copy the result and paste it as values


Edit: you can also use just the right function:



="00:"&RIGHT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-SEARCH(":",TEXT(A1,"h:mm:ss")))





share|improve this answer


























  • You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

    – mewX Max
    Jan 3 at 10:52






  • 1





    Glad it helped. I added another option to the answer because you only need the right function for the purpose.

    – Ali NasserEddine
    Jan 3 at 11:24
















1














This formula should work:



="00:" & MID(TEXT(A1,"h:mm:ss"),SEARCH(":",TEXT(A1,"h:mm:ss"))+1,3) & RIGHT(TEXT(A1,"h:mm:ss"),2)


The important element is to convert the time into a text string.




  • Change A1 to the first cell of Duration (Duree) & copy the formula downward

  • Then, you can copy the result and paste it as values


Edit: you can also use just the right function:



="00:"&RIGHT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-SEARCH(":",TEXT(A1,"h:mm:ss")))





share|improve this answer


























  • You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

    – mewX Max
    Jan 3 at 10:52






  • 1





    Glad it helped. I added another option to the answer because you only need the right function for the purpose.

    – Ali NasserEddine
    Jan 3 at 11:24














1












1








1







This formula should work:



="00:" & MID(TEXT(A1,"h:mm:ss"),SEARCH(":",TEXT(A1,"h:mm:ss"))+1,3) & RIGHT(TEXT(A1,"h:mm:ss"),2)


The important element is to convert the time into a text string.




  • Change A1 to the first cell of Duration (Duree) & copy the formula downward

  • Then, you can copy the result and paste it as values


Edit: you can also use just the right function:



="00:"&RIGHT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-SEARCH(":",TEXT(A1,"h:mm:ss")))





share|improve this answer















This formula should work:



="00:" & MID(TEXT(A1,"h:mm:ss"),SEARCH(":",TEXT(A1,"h:mm:ss"))+1,3) & RIGHT(TEXT(A1,"h:mm:ss"),2)


The important element is to convert the time into a text string.




  • Change A1 to the first cell of Duration (Duree) & copy the formula downward

  • Then, you can copy the result and paste it as values


Edit: you can also use just the right function:



="00:"&RIGHT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-SEARCH(":",TEXT(A1,"h:mm:ss")))






share|improve this answer














share|improve this answer



share|improve this answer








edited Jan 3 at 11:23

























answered Jan 3 at 10:38









Ali NasserEddineAli NasserEddine

17816




17816













  • You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

    – mewX Max
    Jan 3 at 10:52






  • 1





    Glad it helped. I added another option to the answer because you only need the right function for the purpose.

    – Ali NasserEddine
    Jan 3 at 11:24



















  • You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

    – mewX Max
    Jan 3 at 10:52






  • 1





    Glad it helped. I added another option to the answer because you only need the right function for the purpose.

    – Ali NasserEddine
    Jan 3 at 11:24

















You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

– mewX Max
Jan 3 at 10:52





You can't be more right with what you said but after replacing every functions by their French brother, I have something very close of what I wanted : 00:42:00 So I think I'll be able, with your formula, to find what I wanted. When I'll have it, I'll come back in order to answer the question.

– mewX Max
Jan 3 at 10:52




1




1





Glad it helped. I added another option to the answer because you only need the right function for the purpose.

– Ali NasserEddine
Jan 3 at 11:24





Glad it helped. I added another option to the answer because you only need the right function for the purpose.

– Ali NasserEddine
Jan 3 at 11:24


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • 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














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54020432%2fchange-xxxx00-to-00xxxx%23new-answer', 'question_page');
}
);

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







Popular posts from this blog

MongoDB - Not Authorized To Execute Command

Image
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I have successfully enabled authorization on MongoDB and I have created an account on the admin database and then I created an account for my database called test. The following connection string to connect to my test database works successfully: mongo --host 192.168.17.52 --port 27017 -u user1 -p password --authenticationDatabase test Only problem I have now is, I cannot execute commands such as: show dbs. I get the following error when I try to do so: "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0, lsid: { id: UUID("a1d5bc0d-bc58-485e-b232-270758a89455") }, $db: "admin...
Read more

How to fix TextFormField cause rebuild widget in Flutter

Image
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I've got the same issue at #9280. I tried all solutions from that topic with/without GlobalKey for Scaffold and Form but the problem wasn't fixed. I tried to build a authentication form with login and signup. But the keyboard doesn't show when I tap the TextFormField at the first time. Then I try to input some letter from physical keyboard, the widget is rebuilt. Like this: login screen Nothing to be log when I run flutter run --verbose, so I logged manually. Every tapping on field, the widget called dispose -> init -> build. [√] Flutter (Channel beta, v1.0.0, on Microsoft Windows [Version 10.0.17763.195], ...
Read more

in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith

Image
1 I tried to upgrade a yummy sandwich made of two test slices (@JsonTest and @JdbcTest in my case, crunchy test code in between) adding spring boot 2.1 flavour to it. But it seems it was not much of a success. I cannot annotate my tests with many @...Test since they are now each bringing their own XxxTestContextBootstrapper. It used to work when they all used same SpringBootTestContextBootstrapper. @RunWith(SpringRunner.class) @JdbcTest @JsonTest public class Test { @Test public void test() { System.out.printn("Hello, World !"); } } The error I get from BootstrapUtils is illegalStateException : Configuration error: found multiple declarations of @BootstrapWith for test class I understand I might be doing something wrong here but is there an easy way I could load both Json and Jdbc contex...
Read more