Are you required to use Excel for this? I tried entering "9:00AM" into a blank LO Calc spreadsheet, and it automatically recognized the time value. I entered the same into Excel and Excel did not recognize the time, but entered it as a text string. The key to being able to perform calculations on these values is to get them entered as numbers and not text.

Are you required to use a 12 hour clock for these entries? I find that it is a lot easier to enter times in a 24 hour clock, eliminating the need for the AM/PM indicator.

If you must use Excel and you must use a 12 hour clock, then I would suggest one of two things:

1) Data validation to force the user to enter the space so Excel can correctly recognize the time entry.
2) Downstream formulas or macros to correct the user's entry and convert it from text to a number.

What approach do you like for this?