This is one of those problems where I've tried to search the help and search Google but I don't know how to explain it concisely.

I've seen this on a number of spreadsheets that are heavily used: when you try to enter a formula (or a non-text value, such as a date) Excel behaves as though you've entered a text value.

I'm not putting an apostrophe before the value. And when I use Clear -> Formats and reenter the formula, date or number, I'm able to enter non-text values.

Has anyone seen this? I'll try to provide an example spreadsheet.

Hi

When a cell is formatted as text or in cells of certain Excel files downloaded from databases, data warehouses, ERP and others a formula and anything else is considered as a text string.

You need to change the format to "General" in these cells before you try to enter a formula.

The CTRL/Colon does enter the date but it is justified to the left.
The CTRL/Semi-Colon does enter the time but it is justified to the left.

Hmm... I'm doubtful that the spreadsheets I've seen were downloaded from databases.

Yeah, that's probably what is happening when I choose Clear->All. But selecting General changes far less, so that's good to know.

Thanks for the info!