Format specific cells in a column I have a column oof times in different formats

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 929

Explanation / 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...

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.