I need to change String in D column(example 28/10/2018:01:51:29) into Time format.
I've tried:
Format cells and make my own formating(dd/mm/yyyy:hh:mm:ss)
Data->Text to columns
But neither worked
Asked
Active
Viewed 112 times
2
CallumDA
- 12,025
- 6
- 30
- 52
-
1If your string is in `D1`, can you try `=DATEVALUE(LEFT(D1,10))+TIMEVALUE(RIGHT(D1,8))` in a new column? – BigBen Mar 23 '19 at 15:49
-
I've alredy tried something similar, when y put your formula in new colum pop-up window shows and says there is something wrong with the formula – Daniel Armand Velazquez Mar 23 '19 at 15:52
-
Can you share the exact formula you're using? Do all the strings in column D follow the dd/mm/yyyy:hh:mm:ss formatting? – BigBen Mar 23 '19 at 15:53
-
i've used your formula `=DATEVALUE(LEFT(D1,10))+TIMEVALUE(RIGHT(D1,8))` , yes everything in column D is dd/mm/yyyy (example 28/10/2018:12:04:17) – Daniel Armand Velazquez Mar 23 '19 at 15:54
-
What language is your version of Excel in? – BigBen Mar 23 '19 at 16:00
-
My language is set to Czech – Daniel Armand Velazquez Mar 23 '19 at 16:02
1 Answers
1
Your issue is the colon : between the date and the time. Try this:
=TIMEVALUE(SUBSTITUTE(D1,":"," ",1))
This will return a number, like 0.077418981 which Excel can interpret as a time if you format the cell as time. If you want to skip this step and see the time as a string, use an additional TEXT function
=TEXT(TIMEVALUE(SUBSTITUTE(D1,":"," ",1)),"hh:mm:ss")
In Czech:
=ČASHODN(DOSADIT(D1,":"," ",1))
=TEXT(ČASHODN(DOSADIT(D1,":"," ",1)),"hh:mm:ss")
You may also need to swap commas , for semi-colons ; if your regional settings require it:
=ČASHODN(DOSADIT(D1;":";" ";1))
=TEXT(ČASHODN(DOSADIT(D1;":";" ";1));"hh:mm:ss")
CallumDA
- 12,025
- 6
- 30
- 52
-
Am I misreading the question? I understood that OP wanted to maintain the date (28/10/2018) portion as well, based on the comment "make my own formating(dd/mm/yyyy:hh:mm:ss)". Otherwise, good call on the Czech translation and note about semicolons. – BigBen Mar 23 '19 at 16:11
-
@BigBen, it's not entirely clear. However, the first line seems to be the request. I guessed making his "own formatting" was an attempt to explain to Excel what format his data was in? no idea really. – CallumDA Mar 23 '19 at 16:14
-
1However, the SUBSTITUTE function turns his string into a workeable date regardless of where he's ultimately trying to do with it :) – CallumDA Mar 23 '19 at 16:15
-
To clear my question: I needed to convert STRING to TIME(also sorry for unclear question im not native speaker and also not pro in excel) Thank you:) – Daniel Armand Velazquez Mar 23 '19 at 16:24