Search code examples
exceltimestampstring-to-datetime

Changing String into Time

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

Solution

  • 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")

    Translations