Format time from military time to standard time

Excel doesn't know you are trying to enter a time, so it will never interpret 1400 as 2:00 PM. The only way to do this is to either add another column that provides a conversion

Formula:

=TIME((A1-MOD(A1,100))/100,MOD(A1,100),0)

or use a macro to do the conversion in the same cell.