Forum Discussion
Setting one fixed format of date in Excel Cells
Hello there,
I'm trying to set only one format of date acceptable into excel cells. In no instance a user shall feed in a different format of a date or even if he/she does, Excel sheet must be able to auto correct and set it back to the acceptable format.
I've tried 'Format Cells' and Data Validation which were of no help.
The acceptable format I'm trying to freeze is DD Month YYYY (Eg. 12 June 2019)
Please enlighten me with more options if available or to use data validation in excel to achieve.
Thank You
Deleted Here's en example using this code.
18 Replies
Deleted
Conditional formatting appears to work.
Number formatting set as a conditional format to any positive number greater than 30000 (say) will override any input default or user setting.
That is, until someone overwrites the conditional format of course.
PeterBartholomew1 Thank you, will have to try and let you know.
Deleted Change the code to match the code below and format your cells with the proper date format.
Option Explicit Private Sub DTPicker1_Change() ActiveCell.Value = CDate(DTPicker1.Value) End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("A:A")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .Value = Target.Value Else .Visible = False End If End With End Sub
- This is far from simple to make absolutely 100% secure, as it is easy to break a conditional formatting rule by a simple copy and paste.
One way to make pasting harder is by merging two cells next to each other before setting up the Validation on the merged cells, unlocking the merged cell (and setting a date format) and then protecting the worksheet.- More or less, if it is entered in a form Excel can turn into a date it will do so, but you risk Excel getting it wrong of people type dates in the wrong day/month order. There is no fool-proof way to avoid that other than by using VBA I think. Even then there are always ways around it. Database applications are much better equipped for a task like this.
