Excel 2013-Force Date Format in a Cell

Question

So, I recently ran into an issue with a customer where a column (END DATE) was formatted to be a date using the (Format Cells, Date, Type "DD-MMM-YY"), but when a user was typing in a date, they entered (1 Mary 18), accidentally adding an "r" to may. The spreadsheet accepted this, and the user moved on, not noticing the error. This simple fat-finger wrecked all of the dependent cells and caused big problems, resulting in "#value" being displayed and the automation of the worksheet totally failing. Of course upon review I found the error, but as this worksheet grows, it will become increasingly more time consuming to find something like this (needle in a hay stack)

I'm certain there is a way to poka-yoke this through custom Data Validation, but I have not been able to figure it out. Any help would be much appreciated.

Answer 1

Data Validation takes care of this but you also need to protect the sheet if you want to prevent anyone bypassing the validation.

1. Select the data you want to restrict
2. On the Data tab, click on Data Validation
3. In the resulting dialog box, change Allow to Date
4. Optionally set earliest and maybe latest date restrictions
5. Optionally chose whether you allow blanks and set input/error messages if needed.
6. Click OK

To prevent people from undoing the protections, from the Home tab, select Format then Protect Sheet.

Apologies, the above is taken from Excel 2016 which is all I have to hand right now. I think the menu's are in the same place for 2013.