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



Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/15/21

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Hollie Green
    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)), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!