Problem with TEXT() function

[kamahltew]

Hi, from my file in row 3 (Input number ===>) I try to use TEXT to change the number in above to my format text

Problem
- when I try to input the number in cell C3 (in sample file is 835) and I want to change to 8:35 in cell C4 but anything wrong ? it error,
-because when I open it in my boss notebook is usual.

[JBeaucaire]

In C4: =IFERROR(TIMEVALUE(LEFT(C3, LEN(C3)-2)&":"&RIGHT(C3,2)), 0)

Format that cell as Custom: h:mm

Then copy that across through X4.

[shyamhappy]

=LEFT(C3,LEN(C3)-2)&"." & RIGHT(C3,2)

This will help you in same objective.

_________________________________________________________________________________________________
Please click * if something has helped.

I have not failed. I've just found 10,000 ways that won't work.

Thomas A. Edison

[shyamhappy]

Sorry

=LEFT(C3,LEN(C3)-2)&":" & RIGHT(C3,2)

[kamahltew]

I ask somethings ?, why old format in cell C4 is not correct?

[JBeaucaire]

Excel stores TIME values as fractions of the number 1.

0 = midnight
.25 = 6am
.5 = noon
.75 = 6pm

The formula given in C4 results in a numeric value of 0.357638889. When you highlight that cell and then apply a TIME format of h:mm it will display that number to you as 8:35. Just what you wanted.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.