Format specific cells in a column I have a column of times in different formats.

Question

Format specific cells in a column

I have a column of times in different formats. Some are simply military time in the format of a number (ie 1543), and some have 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 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 entered using valid Time syntax -- 23:00, 15:35, etc. When entered as 2300 or 1535 they can be interpreted only as numerical values of two thousand three hundred & one thousand five hundred thirty-five. Simply formatting the cells as Time will not [correctly] convert the 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.