Convert Date and Time to 24 Hour
What formula will help me convert date and time to 24 hour format?
The column I want to convert to 24 Hour format is "Time OnSite"
Answers

Hi @Mark P
Hope you are fine, could you please try the following:
1 Create a helper column (Text/Number) type call it [AM PM] and use the following formula for it and convert it to column format formula:
=IFERROR(RIGHT([Time OnSite]@row, 2), "")
2 Create a helper column (Text/Number) type call it [Hours] and use the following formula for it and convert it to column format formula:
=IFERROR(MID([Time OnSite]@row, (FIND(":", [Time OnSite]@row)  2), 2), "")
3 Create a helper column (Text/Number) type call it [Minute] and use the following formula for it and convert it to column format formula:
=IFERROR(MID([Time OnSite]@row, FIND(":", [Time OnSite]@row) + 1, 2), "")
4Create a helper column (Text/Number) type call it [Time OnSite(24h Format)] and use the following formula for it and convert it to column format formula:
=IFERROR(LEFT([Time OnSite]@row, 10) + " " + IF([AM PM]@row = "AM", Hours@row + ":" + Minute@row, VALUE(Hours@row) + 12 + ":" + Minute@row), "")
we can merge the formulas above to reduce the helper columns we added but i keep it like that to make it easier to be understood by the new members in smartsheet.
the following screenshot shows the result:
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

When I used the above formulas my military time did not work correctly for anything that fell between 12 PM and 1 PM so I changed the hour helper column to include an additional if statement to get it to convert correctly.
=IFERROR(IF(MID(Created@row, (FIND(":", Created@row)  2), 2) = 12, 0, MID(Created@row, (FIND(":", Created@row)  2), 2)), "")
Help Article Resources
Categories
Check out the Formula Handbook template!