format time into military format

[dainova]

Hi,
I'm helping one non profit with they donation pickups, they getting some pickup schedule and need to sort it and put time into military format, eg.
2:30pm --> 14:30
And it doesn't work for me, I do Format Cell > time > 13:30 but my "2:45pm" stays as is, is it possible to accomplish, ideally with macro. I also tried to do Format > Protection > Unlock those columns, but still no result.
Original list composed by diff people so we can't rely on any standard, so I try to take existing file and apply formatting in one place before processing.

Appreciate your help, Excell is really tricky for some users, I'm using either 2007 or 2010.


Best
Dai

[vlady]

format using custom format [h]:mm

[dainova]

format using custom format [h]:mm

Tx much, tried to do all custom format, still 1:30pm stays like a rock, maybe there is another workaround how to sort it in newest=olderst order, I can't see this option in Sort button either.
This is crazy, maybe I have some student office 2010? I don't see this for sort, here is my registration for office:
SKU ID: 6f327760-8c5c-417c-9b61-836a98287e0c
LICENSE NAME: Office 14, OfficeProPlus-KMS_Client edition
LICENSE DESCRIPTION: Office 14, VOLUME_KMSCLIENT channel
LICENSE STATUS: ---LICENSED---

And this is just Time column, no date, even assumed it's the same day, I also tried to do Text-to-Column but there is nos option for TIme format, only date.

Tx.Dai

[TMS]

If you format the cell with the time in it as General, what do you see? If it stays the same, it's not a time (number), it's text that looks like a time.

Regards, TMS

[dainova]

Yes, if General it stays the same 1:30pm, I also tried to do CNTRL + tick and it's the same.
I tried to change format many times and still can't change it.

Dai

[shg]

Select the column and replace "pm" with " pm" (no quotes) to convert to times. Then format the column.

[dainova]

Thansk, Texas !!!!
Yes, am/pm was attached without space so I could not reformat it, now it's fine,
Thanks all

Dai

[Cutter]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save