Number to time conversion

[ahdoj]

I'm sure the answers here, but I can't sort it out.

I have data entered as general number with no punctuation that needs to be
converted to military time. How do I convert it without losing the time
entered?

 

[T. Valko]

How about posting *SEVERAL* representative samples and let us know what
results you expect.

 

[מיכ×ל (מיקי) ×בידן]

I suppose you will have to run some calculations of those cells - therefor,
if cell A1 holds: 1300 then in cell B1 type:
=1*(REPLACE(REPT(0,4-LEN(A1))&A1,3,0,":"))
and custom format cell B1 as: hh:mm
If you don't need to run any calculations - try custom format cell A1, as:
##\:##
(without the need of B1)
Micky

 

[David Biddulph]

If you've got 1730 and you want 17:30, use =--TEXT(A2,"00\:00") and format
as time.

 

[Fred Smith]

Assuming you're entering hours and minutes (no seconds), convert with:
=time(a1/100,mod(a1,100),0)

Regards,
Fred