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!

  • 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

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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, 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 "Thumbs Up" icon, below, in the bottom right corner:)

  • Great ...!!! :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!