Military Time to Standard Time

There are several articles about how to convert standard time to Military Time but I cannot find anything that tells me how to convert Military Time into Standard Time. Ex: If I enter 16:13 in Column A, I want Column B to display 4:16 PM. Can anyone help me with that?


Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @McAfee Electric Mgr Try this:

    =IF(ISBLANK([Column A]@row), "", IF(VALUE(LEFT([Column A], 2)) < 12, (VALUE(LEFT([Column A], 2)) - 12) + RIGHT([Column A], 3) + " PM", IF(VALUE(LEFT([Column A], 2)) = 12, [Column A] + " PM", [Column A] + " AM")))

    Logic: IF Column A is blank, leave this cell blank; otherwise, if the numeric value of the left two characters of Column A is greater than 12, subtract 12 from that value, then join the result with the rightmost 3 characters, and add " PM"; If the numeric value of the left two characters of Column A is equal to 12, then just join " PM" to end of the value from Column A; otherwise, just join " AM" to the of the value from Column A.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thank you Jeff! Can you take a look at this screen shot and see what I have wrong. Coming back as unparseable.


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @McAfee Electric Mgr My mistake - I left the @row off most of the [Column A] references:

    =IF(ISBLANK([Column A]@row), "", IF(VALUE(LEFT([Column A]@row, 2)) < 12, (VALUE(LEFT([Column A]@row, 2)) - 12) + RIGHT([Column A]@row, 3) + " PM", IF(VALUE(LEFT([Column A]@row, 2)) = 12, [Column A]@row + " PM", [Column A]@row + " AM")))

    Sorry about that!

    Note - if properly formatted, cell references will always turn a different color as you are creating a formula, and a matching box will appear around the referenced cell. See how the first [Column A]@row was changed to blue?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • We are getting closer! But now instead of 13:00 calculating to 1:00 PM I am getting 13:00 AM


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!