# Military Time to Standard Time

Options

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

• ✭✭✭✭✭✭
Options

@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

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

• Options

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

• ✭✭✭✭✭✭
Options

@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

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

• Options

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

• edited 06/18/24
Options

I know this post is older, but since I encountered the same result (a PM time showing 13:00 AM), I thought I'd note the correction if anyone else comes along needing this formula. Instead of <12, change it to >12. That should give you the desired standard time format 1:00 PM:

=IF(ISBLANK(End@row), "", IF(VALUE(LEFT(End@row, 2)) > 12, (VALUE(LEFT(End@row, 2)) - 12) + RIGHT(End@row, 3) + " PM", IF(VALUE(LEFT(End@row, 2)) = 12, End@row + " PM", End@row + " AM")))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!