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
|