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.

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

On Mar 4, 10:37*pm, mikerobe wrote:
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.

*Maybe* this:

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

Then format as TIME 13:30


--
Biff
Microsoft Excel MVP


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

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" wrote in message
...
*Maybe* this:

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

Then format as TIME 13:30


--
Biff
Microsoft Excel MVP


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

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)


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
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" wrote in message
...
*Maybe* this:

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

Then format as TIME 13:30


--
Biff
Microsoft Excel MVP


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

On Mar 4, 10:50*pm, "T. Valko" wrote:
*Maybe* this:

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

Then format as TIME 13:30

--
Biff
Microsoft Excel MVP

"mikerobe" wrote in message

...



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.- Hide quoted text -

- 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

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

On Mar 5, 11:33*am, mikerobe wrote:

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

On Mar 5, 11:38*am, Pete_UK wrote:
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

On Mar 5, 11:33*am, mikerobe wrote:





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- Hide quoted text -

- Show quoted text -

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

You're welcome, Mike.

Pete

On Mar 5, 11:41*am, mikerobe wrote:

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

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

Even shorter, Rick !! <bg

Pete

On Mar 5, 7:03*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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