First, how you format a cell doesn't affect how XL "accepts" a
subsequent entry (except if you set it to Text, which bypasses the
parser).

See

http://cpearson.com/excel/DateTimeEntry.htm

for one technique that you could modify (e.g., by removing punctuation
first).


In article ,
Nevets wrote:

I am creating a workbook for many users, so I would like to make it as "idiot
proof" as possible. In several of the cells, I want users to enter time in a
24 hr format. If I format the cell to accept "hh:mm" times, then the users
have to enter it exactly that way for it to be accepted. Is there a way to
set up the cell so that if they enter "hhmm", or "hh.mm", (or even "hmm" if
the time being entered is before 0959), the program will convert it to
"hh:mm" format when they tab out of the cell?
For example, user wants to enter 08:00 hrs; so if they type 08:00, 08.00,
0800, or 800 in the cell, when they tab out, it will convert to 08:00.
I also want to do something similar with a date field. MS Word does
something like this with text form fields; I can set up a text field such
that if someone enters "sep 7 07" then tabs out of the field, it will be
changed to "Friday September 7, 2007".
Can I get Excel to do what I want?