Formula to convert military time into hh:mm format

[javiere77]

Hello, I am looking for a formula that converts military time into hh:mm format (I only need hrs and min)

For ex. If I type 600 in a cell I want the formula to automatically change it to 6:00 am, or if I write 1800 it changes to 18:00.

I have looked for it before but haven't really found something that I can understand or that serves my purpose.

I appreciate your help and responses!

[Jonmo1]

Try

=TEXT(A1,"00\:00")+0

[GC Excel]

Hello and welcome to the forum,

Here's a formula you can try:

Please Login or Register  to view this content.

And then format you cell with a custom time format h:mm

[José Augusto]

Hi
Use this format
#0":"00

[javiere77]

This one seemed to work the best, is there a way to add am/pm after??

[javiere77]

Hi
Use this format
#0":"00

This one seemed to work the best, is there a way to add am/pm after?

[shg]

If you use that method, the cell value is unchanged, it just looks different. I suggest Jonmo's formula, then format the result as h:mm am/pm