Number to time

[mikerobe]

Hi
An information system I work with outputs times as follows to a csv
file.

748 for 7:48
1236 for 12:36
1436 for 14:36
etc

It leaves out the colon. How can I change the values to represent
times instead of numbers. I am thinking along the lines of a function
using RIGHT function but cannot get it to work any help is appreciated.

 

[Pete_UK]

Assuming your "times" are in column A, put this formula in an adjacent
column:

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

Format the cell as Time using [h]:mm, then copy down the column.

Hope this helps.

Pete

 

[T. Valko]

*Maybe* this:

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

Then format as TIME 13:30

 

[Meebers]

T.. what does the "--" do in this formula. Seems to format the result to
the right! and also why is "\" needed in the format part.

 

[T. Valko]

The TEXT function returns a text string.

A1 = 748

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

Returns 07:48 as a TEXT string. It's not a true Excel numeric time value.

The "--" coerces this text string to the true Excel numeric time value of
7:48 AM (or 7:48 h:mm).

The "\" is a "delimiter". It tells the TEXT function to "split" the string
748 into a format like this ##:## (or hh:mm)

 

[mikerobe]

*Maybe* this:

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

Then format as TIME 13:30

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thanks Biff this worked perfect, Petes solution worked also, two
different ways of doing the same thing, never came across Biffs way of
doing this before so will use this solution
Much appreciated
Eddie

 

[Pete_UK]

You said you wanted a formula which included the RIGHT function, so I
gave you that. There are other ways still, but Biff's is probably
shortest.

Thanks for feeding back.

Pete

 

[mikerobe]

You said you wanted a formula which included the RIGHT function, so I
gave you that. There are other ways still, but Biff's is probably
shortest.

Thanks for feeding back.

Pete



- Show quoted text -

Thanks Pete, I would have been messing about with this for a long
while without yours and Biffs help.

 

[Pete_UK]

You're welcome, Mike.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Thanks Biff this worked perfect, Petes solution worked also,

two different ways of doing the same thing, never came across
Biffs way of doing this before so will use this solution

While it doesn't harm anything, Biff's formula has an extraneous set of
parentheses in it. This version of it will work just as well...

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

Rick

 

[Pete_UK]

Even shorter, Rick !! <bg>

Pete

While it doesn't harm anything, Biff's formula has an extraneous set of
parentheses in it. This version of it will work just as well...

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

Rick