I have a list of numbers:

900
1030
830 etc.

I need these in the format:

09:00:00
10:30:00
08:30:00 etc

I have tried text to columns but without the 0 before 830 the fixed width
line doesnt fall right.

Very stuck on this so any help would be gratefully recieved (otherwise I
have alot of work on my hands!!)

Assumes C5 has 900 and Time format set to 00:00:00

HTH Rob

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

"FishandChips" wrote in message
...
I have a list of numbers:

900
1030
830 etc.

I need these in the format:

09:00:00
10:30:00
08:30:00 etc

I have tried text to columns but without the 0 before 830 the fixed width
line doesnt fall right.

Very stuck on this so any help would be gratefully recieved (otherwise I
have alot of work on my hands!!)

Hi,

Assuming your numbers are in a1 down try this

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

Format as time
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"FishandChips" wrote:

I have a list of numbers:

900
1030
830 etc.

I need these in the format:

09:00:00
10:30:00
08:30:00 etc

I have tried text to columns but without the 0 before 830 the fixed width
line doesnt fall right.

Very stuck on this so any help would be gratefully recieved (otherwise I
have alot of work on my hands!!)

900
1030
830 etc.

What times do those represent?

Is 900 9:00 AM or 9:00 PM
Is 1030 10:30 AM or 10:30 PM?
etc

--
Biff
Microsoft Excel MVP


"FishandChips" wrote in message
...
I have a list of numbers:

900
1030
830 etc.

I need these in the format:

09:00:00
10:30:00
08:30:00 etc

I have tried text to columns but without the 0 before 830 the fixed width
line doesnt fall right.

Very stuck on this so any help would be gratefully recieved (otherwise I
have alot of work on my hands!!)

=--TEXT(A2,"00\:00") and format as time.
--
David Biddulph

FishandChips wrote:
I have a list of numbers:

900
1030
830 etc.

I need these in the format:

09:00:00
10:30:00
08:30:00 etc

I have tried text to columns but without the 0 before 830 the fixed
width line doesnt fall right.

Very stuck on this so any help would be gratefully recieved
(otherwise I have alot of work on my hands!!)