number to time format when digit length varies

[UM Win]

I am struggling to convert following "number" format cells as 24 hrs "time"
format (hh:mm)

516
2306
637
202
2353
300
3
6

It should then appear as:
05:16
23:06
06:37
02:02
23:53
03:00
00:03
00:06

Any help will be a great relief.

Thanks/regards,

 

[Peo Sjoblom]

You can't use format to do this, you would need a formula (or a macro) and
help cells, then you can copy and paste as special over the old values, with
your value in A1

=(INT(A1/100)+MOD(A1,100)/60)/24

format as hh:mm

--
Regards,

Peo Sjoblom

(No private emails please)

 

[UM Win]

It worked. Your kind help saved my day(s).

Thank you so much !

Regards,

UM Win
--

 

[Gary Smith]

This does the same thing, but may be a bit more intuitive:

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

 

[Dave Peterson]

One more:

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

And format as hh:mm

 

[Peo Sjoblom]

Maybe but it will format AM and PM automatically while my formula will give
the option to the regional settings and if the time is more than 24 hours
like 2530 your formula will only return 01:30 even if it is formatted as
[hh]:mm

--
Regards,

Peo Sjoblom

(No private emails please)