# 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 ...

Hope this will help

## Files

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

• 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 "Smiley" icon, below, in the bottom right corner

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 "Smiley" icon, below, in the bottom right corner

• Beautiful, thank you so much! Saved me tons of time! Kind regards, Michael

• Great ...!!!

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

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" 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!