How to format a range of numbers (single column) to time format in MS Excel

[Sam96434]

Guys,
Can any one of you tell me how I can convert or format a range o
numbers in a single column into a range of times (time format)? Is i
possible?
Example.. 0945 - 1000 is to be converted to 9:45 - 10:0

 

[Bob Phillips]

Hi Sam,

Here's some code to do it

Dim cell As Range

For Each cell In Selection
cell.Value = TimeSerial(cell.Value \ 100, cell.Value Mod 100, 0)
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Sam
Here's a way to do it with a formula

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

You can enter this formula in a seperate column, fill down, and then copy and paste_special over the original data

Good Luck
Mark Graesse
(e-mail address removed)

----- Sam96434 > wrote: ----

Guys
Can any one of you tell me how I can convert or format a range o
numbers in a single column into a range of times (time format)? Is i
possible
Example.. 0945 - 1000 is to be converted to 9:45 - 10:0

 

[Dave Peterson]

One more that just looks like a range of time:

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

But I think I'd put each time in a separate cell:

=--TEXT(--(LEFT(A1,4)&"00"),"00\:00\:00")
and
=--TEXT(--RIGHT(A1,4)&"00","00\:00\:00")

And format each as hh:mm

When the values are really time, you'll be able to do other things (date/time
arithmetic) with them.

 

[Sam96434]

Thanks Mark for the formula. But I would like to know how you can
generalize for all the rows. Because each time, you have to change the
row number.
for example

LEFT(A1,2)&":"&RIGHT(LEFT(A1,4),2)&
in this expression..A1 is the value of column A - row 1. So I would
have to keep on changing the row numbers to apply it to all rows. I
hope you understand my question.
Thanks so much.
Sam.

 

[Sam96434]

Thanks Mark for the formula. But I would like to know how you ca
generalize for all the rows. Because each time, you have to change th
row number.
for example

LEFT(A1,2)&":"&RIGHT(LEFT(A1,4),2)&
in this expression..A1 is the value of column A - row 1. So I woul
have to keep on changing the row numbers to apply it to all rows.
hope you understand my question.
Thanks so much.
Sam

 

[Sam96434]

Thanks Mark and Philip for the answers..I really appreciate you
knowledge.
Sam