convert text into date

[Guest]

can any one help me
i have data in excel like 436,or 1851 i wanted to convert this text format
into time and date format so actully i wanted it to be as 4:36 AM or 18:51 PM,
I found one solution if you manually put ":" between "4" and "36" then
format cell as time, this will work but i have a lot of data, is there any
formula to put ":" between charactors
thanx
shabbir

 

[Frank Kabel]

Hi
in adjancent cell try the following formula
=TIME(LEFT(A1,1+(LEN(A1)=4)),RIGHT(A1,2),0)
and format this cell as time

 

[Myrna Larson]

In addition to Frank's method, another way to determine the values for the
hour and minute is like this:

=TIME(INT(A1/100),MOD(A1,100),0)