Convert Date and Time to 24 Hour

Mark P

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 On-Site"

Bassam Khalil

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 On-Site]@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 On-Site]@row, (FIND(":", [Time On-Site]@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 On-Site]@row, FIND(":", [Time On-Site]@row) + 1, 2), "")

4-Create a helper column (Text/Number) type call it [Time On-Site(24h Format)] and use the following formula for it and convert it to column format formula:

=IFERROR(LEFT([Time On-Site]@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:

Hollie Green

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)), "")