Military Time to Standard Time

McAfee Electric Mgr

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!

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.

McAfee Electric Mgr

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

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?

McAfee Electric Mgr

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

Gwendolene Day

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