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,

number to time format when digit length varies
 

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" wrote in message
...
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,

number to time format when digit length varies
 

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

Thank you so much !

Regards,

UM Win
--

"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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,

number to time format when digit length varies
 

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 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

number to time format when digit length varies
 

One mo

=--(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

number to time format when digit length varies
 

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" wrote in message
...
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 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