Text "12:00 AM" to Time Format

[mdalby]

I have some data that was text and in time format such as:

12:00 AM
12:30 AM
1:00 AM
1:30 AM

When I change the format of the column to Time it is still behaving a
text. If I sort the column the data is sorting in the followin
order:

1:00 AM
1:00 PM
1:30 AM
1:30 PM

Even if I paste the columns data into a new spredsheet that already ha
the column format in time it will not behave as a time field.

Is there a way to convert the text data to time?

Thanks in advance.

M

 

[Frank Kabel]

Hi
after changing the format try the following:
- select an empty cell and copy this cell
- select your values and goto 'Edit - Paste Special' and
choose 'Add'

 

[mdalby]

Frank,

That worked on some of the cells but not all of them. Any other ideas

 

[upstate_steve]

Insert a new column and use TIMEVALUE(reference).

This will convert text to time in Excel's serial number format.

So, if the text string "12:00 PM" is in A1, TIMEVALUE(A1) returns 0.5
which can be formatted as time.

Enter the formula, drag down, give the column a time format, selec
column, copy, paste special, values, delete original column.

--Stev

 

[Frank Kabel]

Hi
you may also try using 'Data - Text to Columns'. just step through the
wizard.
Sounds like your data is stored as text and does also contain
additional spaces

 

[mdalby]

Frank,

That was the ticket. I received a file from someone else and someho
they had a space in front of the times.

Can you explain to me what process does that you explained as far a
taking a blank cell and adding through Paste Special Values? How doe
that take care of the convesion to time?

Thanks,

M

 

[Frank Kabel]

Hi
this forces Excel to convert the 'text-number' to a real number as you
apply a mathematical operation on it (adding zero)