Converting time figures into 24-hour format & sorting them...
I've a sheet with - among others - two columns containing:
Column A. Time figures (03:27) and Column B. The "AM" or "PM" designator (text) I want to be able to sort the times in chronological order, by using the 24-hour time format. How can I tell Excel to look at the contents of the two cells in these columns for each of the 1200+ rows in my sheet, and convert any "PM" times to the 24-hour format? For example, for 03:27 PM, I want the "03:27" to read "15:27". Or is there another way to accomplish this? Thanks! ---------------------- Clint Johnson Homewood, IL |
First, you can sort the times as they exist. Sort Column B first, then
Column A. That will put all the AMs first, followed by all the PMs. Second, to convert column A to an excel time, use =--A1 and copy down. To adjust for am/pm, try: =--A1+if(b1="pm","12:00:00",0) -- Regards, Fred Please reply to newsgroup, not e-mail "Clint Johnson" wrote in message ... I've a sheet with - among others - two columns containing: Column A. Time figures (03:27) and Column B. The "AM" or "PM" designator (text) I want to be able to sort the times in chronological order, by using the 24-hour time format. How can I tell Excel to look at the contents of the two cells in these columns for each of the 1200+ rows in my sheet, and convert any "PM" times to the 24-hour format? For example, for 03:27 PM, I want the "03:27" to read "15:27". Or is there another way to accomplish this? Thanks! ---------------------- Clint Johnson Homewood, IL |
All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com