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

 

[honkin]

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")

cheers shadowu459

No, as indicated, I have no idea how this has been format as it is not my spreadsheet. I had not tried the formula you supplied before, but when I did, it merely gives me the result 1.23pm in the cell.

As I mentioned, the closest I have come is deleting the pm manually and then when I convert to General, I get a decimal. When that is converted to a time, I get 01:30 instead of 13:30. Nothing else has even come close yet

cheers

 

[FormR]

Nothing else has even come close yet

Have you tried what I have suggested?

 

[shaowu459]

have you tried below steps? when you see a decimal, do not covert to General, but do as follow steps:
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.

 

[honkin]

cheers again shaowu459

No, none of this gets the correct result. The closest I can get is to get to 01:30 for 1:30pm, but this is hit and miss

So here's what finally fixed it.

Each cell had a space in front of the number, so used TRIM to remove the space. Then highlighted all of the column and copied and pasted special, just as values. Then I used FormR's solution to go replace pm with a space before the pm. Then formatted the column as custom hh:mm.

So many other things had been tried, but failed, but the two tricks in the end were to trim the initial space and copy, then paste special as values and FormR's space before the pm. Took way longer than it should have

Thanks so much for all your help, especially FormR for the tip

cheers

 

[alvisonhunter]

Hello Honkin,

As you've previously tried, using the Text Formula help us format the look and feel of some strings and values even when the conserve their original type in its references. Please try again by using =TEXT(B2,"hh:mm:ss AM/PM") regular time or try with =TEXT(B2,"[hh]:mm:ss") for military time, press Enter key. If needed, drag fill handle down to apply this formula to the below cells. Kindly let us know how it goes on your end, mate.

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

 

[honkin]

Thanks for the reply, alvisonhunter, but as indicated in my last post, the problem is already resolved.

cheers