How can I format cell A1 so that when I enter the time in military format in A1, it will display as standard time in A1.

Example:I would like to enter 1400 in A1 and have the same cell display 2:00PM

Rightclick on the cell. Choose properties. Under "Number" then "time" use format you want.

You cannot accomplish that with formatting alone. The Excel time 14:00 or 2:00 PM is represented internally by a fraction of a day. The number 1400 would be interpreted as DATE(1903,10,31).

You could create an event macro that is invoked each time you enter a value; and there, you can convert the data entry.

Or you could enter the following formula into a parallel column: =--TEXT(A1,"00\:00"). The double negate converts the text into numeric, interpreting 14:00 as time.

In either case, format the cell as Time or Custom hh:mm .

One of the many answers searching the web
http://answers.microsoft.com/en-us/o...6-1cc1de79d2e2

This is a well known problem. Always search the Net first before asking an obvious question

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post