prevent Excel from EVER automatically converting numbers to dates

G

gt

in Excel v. X for Mac how do I permanently prevent Excel from EVER
automatically converting numbers to dates. Formatting individual cells
as text will not work in my applications. I need to disable this funtion.
 
J

J.E. McGimpsey

Not possible - there's no preference option to turn this off.

Instead of preformatting as text, entries can be made with an
apostrophe as a prefix:

'3/4

You might be able to use an event macro to change it back
retroactively, but that depends on knowing what format the user
entered it. For instance,

3/4, 3-4, 4Mar, mar4, etc

will all be interpreted as 4 March 2003 (with US settings), or
serial number 36222. Unfortunately, by the time VBA has access to
the data, there's no clue as to which one was originally entered.
Post back if that's an option.

Depending on your application, another alternative might be to use a
userform for entering data, parsing it in code and putting the
result in the cell.

If there are specific combinations that you use (like 3/4), you can
use an event macro to enter an autocorrect (Tools/Autocorrect) that,
for instance, changes 3/4 to '3/4. You'd want to take them out in
the Workbook_Deactivate event, though, since Autocorrects apply
across the Office suite (e.g., in Word) as well.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top