Convert Text To Time

[honkin]

Hi
I have received a spreadsheet from a colleague which has the time showing as 1:30pm and so on. The cells are formatted as General and no matter what format I try, I have not been able to convert the cells to show 13:30 etc. I even tried

=TEXT(B2,"HH:MM")

to remove the am and pm, but this also failed, with times still showing as 1:30pm etc.

The cells all contained a space at the beginning, so I used the TRIM function to remove that, but now I am stuck with a column I am unable to use when filtering. I tried

=ISNUMBER(B2)

to see if what I was looking at was a number and the response was FALSE, which I assume means it is a text string, but am still stuck.

Any suggestions as to how to get around this at all?

cheers

 

[Jeffrey Mahoney]

I learned a trick to convert "Text" cells to native format. Highlight the cells. Choose DATA-TEXT TO COLUMNS, then press FINISH without making any selections. Ta Da!

 

[honkin]

Cheers Jeffrey

Unfortunately no changes there. What I tried next was to simply delete the pm directly after the time and then when I changed the format to General, it gave me a decimal, which is a good sign. If I convert to time, it gives me 01:30 instead of 13:30, so not sure how to to change it from one to the other. As indicated, I already used the code above, which is supposed to convert from 12 hour to 24 hour time.

There are some 10,000 rows, so not an issue to remove the pm using Find & replace, but then to convert to 24 hours is beyond me after trying the conventional method.

Thank Jeffrey

 

[FormR]

Hi, you could try doing a find and replace. With the find as "pm" and the replace as " pm" (without the quotes) and the same for am.

 

[honkin]

Cheers FormR. I am actually trying to achieve 24 hour time, so 1:30pm to be 13:30, so I actually don't want am or pm to show at all. The closest I have come is to get to the point where when changed to General format, they appear as a decimal, meaning they are now actually Time, rather than a text string. But the time comes up as 01:30 rather than 13:30.

Thanks again

 

[FormR]

You can convert the text times to real times using the method I describe. You can then format them to display the time in 24hr format with a custom number format of hh:mm.

 

[shaowu459]

1)insert a blank between 13:30pm
2)right click on the cell--->Format cells--->in Number caption, click Custom
3)enter h:mm in the text box on the right side, click OK button to finish.

if you want to convert all the 10,000 cells, you can use Find and Replace, replace all am with " am", pm with " pm".

 

[shaowu459]

Book1
	A	B
1	1:30am	1:30
2	2:30pm	14:30
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=TEXT(REPLACE(A1,MIN(FIND({"a","p"},A1&"ap")),," "),"h:mm")

 

[shaowu459]

I made a duplicate post, how could I delete this post?

 

[honkin]

Thanks for the input, FormR, but however these appear to have been formatted, this does nothing at all except put a space after the number.

After adding the space, this is what it looks like formatted as General and when the format is changed to Time or even Custom and a time chosen, it still shows 1:23 pm

Here is how it looks when I remove the pm and space and format it as General

At least as a decimal, it is now closer, but formatting the decimal as time gives 01:30

cheers