I have a text file with times expressed as a numeric - eg 0510. I have a few
hundred lines of input each with this format repeated. I need to change 0510
to 05:10 - ie 10 past 5 in the morning, but when I select the appropriate
time format, it converts it to 00:00 instead of 05:10. Is this automation
possible, perhaps with a custom format or do I have to go through hundreds of
cells putting in a colon?

Rita_H wrote:
I have a text file with times expressed as a numeric - eg 0510. I have a few
hundred lines of input each with this format repeated. I need to change 0510
to 05:10 - ie 10 past 5 in the morning, but when I select the appropriate
time format, it converts it to 00:00 instead of 05:10. Is this automation
possible, perhaps with a custom format or do I have to go through hundreds of
cells putting in a colon?

Hi Rita_H

If your text is in cell A1, try this:

=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

Copy the formula down as necessary and Custom format the result cells
to hh:mm

(Copy and Paste Special Values if you don't want the formula hanging
around.)

Regards

Steve

In a "helper" column try (assuming ALL the values 4 digits ):

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

If everything is OK, then copy and paste special==values in the "helper"
column and delete original.

HTH



"Rita_H" wrote:

I have a text file with times expressed as a numeric - eg 0510. I have a few
hundred lines of input each with this format repeated. I need to change 0510
to 05:10 - ie 10 past 5 in the morning, but when I select the appropriate
time format, it converts it to 00:00 instead of 05:10. Is this automation
possible, perhaps with a custom format or do I have to go through hundreds of
cells putting in a colon?

Hi Rita,

=time(left(a2,2),right(a2,2),0)

hth
regards from Brazil
Marcelo

"Rita_H" escreveu:

I have a text file with times expressed as a numeric - eg 0510. I have a few
hundred lines of input each with this format repeated. I need to change 0510
to 05:10 - ie 10 past 5 in the morning, but when I select the appropriate
time format, it converts it to 00:00 instead of 05:10. Is this automation
possible, perhaps with a custom format or do I have to go through hundreds of
cells putting in a colon?

One mo
=--TEXT(A1,"00\:00\:\0\0")

And format that cell as Time.

Rita_H wrote:

I have a text file with times expressed as a numeric - eg 0510. I have a few
hundred lines of input each with this format repeated. I need to change 0510
to 05:10 - ie 10 past 5 in the morning, but when I select the appropriate
time format, it converts it to 00:00 instead of 05:10. Is this automation
possible, perhaps with a custom format or do I have to go through hundreds of
cells putting in a colon?

--

Dave Peterson