Format specific cells in a column I have a column oof times in different formats
ID: 3564373 • Letter: F
Question
Format specific cells in a column
I have a column oof times in different formats. Soome are simply military time in the format of a number (ie 1543), and some havee colons with seconds indicated (15:43::00). All seconds are 00, so I can disregard them. I want to convert all of these numbers to one format of 15:43.
Time 1519 1707 848 1510 1713 22:26:00 947 1509 1532 2149 854 1020 1012 1855 1713 1703 1026 1313 1214 1418 1458 19:24:00 951 1222 1259 1335 1231 2345 1325 1330 1508 1600 1606 2028 2033 955 1156 2108 1218 941 930 1121 1002 1157 1148 20:53:00 11:31:00 16:23:00 929Explanation / Answer
The article Jim provided the link to needs to be opened using Word or comparable, However, I don't think it will explain your difficulty...
The problem is that none of the entries that lack a colon (:) are able to be parsed by Excel as Time -- 'military' or otherwise -- because they have not been entered in a compliant manner.
Even though spoken as 'twenty-three hundred' or 'fifteen thirty-five' the values still have to be enteredusing valid Time syntax -- 23:00, 15:35, etc. When eentered as 2300 or 1535 they can be inteerpreted only as numerical values of two thousand three hundred & one thousand five hundred thirty-five. Simply forrmatting the cells as Time will not [correctly] convert tthe numerical entries to time values..
Unless someone can provide you with an automated means of properly inserting the colons you'll have to type them into the appropriate locations...