Posts by maztek

Quote from Carim

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

Display More

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

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

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!