Quote:
Originally Posted by CLEMMON
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
|
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 .