How to lock the format that data is entered into a cell

[Issy.87]

Hi,

I would like to 'lock' how data is entered into a cell. I am trying to reduce inputting error and make it only possible to enter data into a particular cell in date format. The reason I am doing this is that certain other formulas in the sheet rely on this data being correct and other users tend to write the date in a different format eg 01/02/2017 verses 01.02.2017. If the data that is entered is correct matches the format then the it should be accepted. If it is entered in an incorrect format I want it to not accept the entry.

Cheers,

[sandy666]

Try Data Validation

• Allow: Date
• Between
• Start date: 01/01/1900
• End date: 31/12/3000
• Ok

it will accept dates in this format or change it to this format (I hope )
If format will be wrong you will get Warning

You can use there Input Message info and Error Alert what's going wrong

Note: DataValidation works this way with manual entering. It doesn't prevent Copy/Paste

[Issy.87]

Thanks Sandy!

[sandy666]

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.