• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing AM and PM times into 24-hour format

estillbham

estillbham

New Member
I have a spreadsheet (by someone else) in which one column contains time entries in the format "3:15 PM." When I sort using this column, 3:15 PM appears before 8:00 AM. I tried setting the format of the column as "Time," but that seems to make no difference. I tried using TIMEVALUE(D2) to copy and change the value to another column, but that returns #VALUE!


Is there a formula I can use to translate all these times into a 24-hour clock?


Ed
 
I had tried setting the Time format to 13:30.


I just tried something else on a new spreadsheet. Column A is formatted for Text and Column B for Time (13:30) format.


If I enter 7:05 AM in A1, shouldn't =TEXT(A1,"TIME") in B2 display at 7:05? Instead it displays #VALUE!
 
Good day estillbham


Not sure what you are doing but the formula works does not matter if the cells are formatted as general or time B2 shows 7:05! the only difference being you can not use a text time in formula to carry out some function.

I used the custom tab to set the date format to hour/mins and am/pm and it then sorts with no problem.
 
Jason,


Thanks. That works on a new spreadsheet -- if I also set the format to 13:30. But it does not work on my existing spreadsheet. That means there is something peculiar about the formatting or something else in that spreadsheet.


Thanks to everyone who has suggestions.


Ed
 
Hi Ed ,


A couple of things to be noted :


1. A time is basically a numeric value ; to find out whether the data you have in a cell is text or numeric , just format it as number , and see what it displays in the Format dialog box.


E.g. if you enter the time 12:35:33 in a cell , it will display exactly that ; now , if you format the cell as Number , it will display 0.52468750 depending on the number of decimal places you have selected.


If the above does not happen , it means the cell was formatted as text , or the time was entered as a text string ; if you format a cell as text , and enter the exact same value 12:35:33 , it will display that , but the data is no longer numeric , it is now a text string.


2. The TEXT function is used to convert a numeric value to text ; thus , if you enter the number 0.52468750 in a cell , say A1 , and in some other cell you put in the formula =TEXT(A1,"hh:mm:ss") , you should see 12:35:33 in that cell. However , this will now be a text string , and cannot be used as a number. The TEXT function , if used on a text string , will not generate an error , but it does nothing since the original data was itself a text string , and did not need any conversion.


Thus , changing the format of a cell will change the displayed value only if the cell originally contained a numeric value.


You first need to verify whether the time that you are seeing is actually a time as a number , or a time as a text string.


Narayan
 
Hi estillbham,


Another point is that whether there is any date attached to the time or not, because when you simply enter time, excel automatically attaches a date to it, so it can make difference when it comes to sort data. Kindly check for that as well.


Regards,
 
You must log in or register to reply here.
Back
Top