Create excel time out of 2 to 4 digit number

  • Dear fellow excellers, I have a bunch of number values representing timestamps, in the format 705 1440 41 I need to convert them to proper excel time format, i e 07:05 14:40 00:41 Have searched various forums and tried REPLACE, LEFT/RIGHT etc, but without much success. Finally made three columns, the first with =IF(LEN(A2)=4;REPLACE(A2;3;0;":");"") , the next with IF A2 LEN is 3, then IF A2 LEN is 2. Then filtered out the values and pasted them to a common column. This gave a column with text like “07:05” from which I could get a TIMEVALUE. This feels like a very crude and slow method – ideally I’d like to build one formula to do it all in one fell sweep. Grateful for any help!

  • Hello and Welcome to the Forum :)


    If you need to use a formula based solution ...


    attached is your test file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Wow, thanks for the fast reply, and great formula! I am afraid that due to the formatting of my post, it appeared that the "time" numbers were in a long sequence in the same cell. However, they are not, I have one value per cell in a column:


    705

    1440

    41


    Can you see a way to deal with it to reflect this?

    Many thanks,

    Michael

  • Hello again,


    Fine ... so it is even a lot easier ...

    Code
    1. =VALUE(LEFT(TEXT(B2,"0000"),2)&CHAR(58)&RIGHT(TEXT(B2,"0000"),2))

    see attached ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Would appear you have downloaded the latest test file ...


    So ... Once you have tested the latest formula ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)