number to time format when digit length varies

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,

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" <noname@UMwin.com> wrote in message
news:%23kT3kz6%23FHA.216@TK2MSFTNGP15.phx.gbl...
>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,
>
>
>
>

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

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

In microsoft.public.excel Peo Sjoblom <terre08@mvps.org> wrote:
> 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

--
Gary L. Smith
Columbus, Ohio

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

Thank you so much !

Regards,

UM Win
--

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:%23WZ$0A7%23FHA.360@TK2MSFTNGP09.phx.gbl...
> 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" <noname@UMwin.com> wrote in message
> news:%23kT3kz6%23FHA.216@TK2MSFTNGP15.phx.gbl...
>>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,
>>
>>
>>
>>
>

One more:

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

And format as hh:mm



UM Win wrote:
>
> 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,

--

Dave Peterson

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)


"Gary Smith" <bitbucket@example.com> wrote in message
news:O0s1mH7%23FHA.1336@TK2MSFTNGP10.phx.gbl...
> This does the same thing, but may be a bit more intuitive:
>
> =TIME(INT(A1/100),MOD(A1,100),0)
>
> In microsoft.public.excel Peo Sjoblom <terre08@mvps.org> wrote:
>> 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
>
> --
> Gary L. Smith
> Columbus, Ohio