Converting whole numbers into time

[Kmitchell]

Is there a quick way to convert the number 1901 to 19:01 so that excel would know it's time related? Same thing with 730 to 7:30.

Thanks in advance.

 

[Jonmo1]

=TEXT(A1,"00\:00")

Formatted as time, not number

 

[nav1701]

If the time is in a1 then

=LEFT(A1,2)&":"&RIGHT(A1,2)

 

[Kmitchell]

Thanks. That works fine for the 19:01 but it converts the 730 to 73:30. Any advice?

 

[Richard Schollar]

Use Jonmo1's formula modified to:

=TEXT(A1,"00\:00")+0

 

[Kmitchell]

This is perfect! Much appreciated!