SOLVED
Home

Setting one fixed format of date in Excel Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-687350%22%20slang%3D%22en-US%22%3ESetting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687350%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20only%20one%20format%20of%20date%20acceptable%20into%20excel%20cells.%20In%20no%20instance%20a%20user%20shall%20feed%20in%20a%20different%20format%20of%20a%20date%20or%20even%20if%20he%2Fshe%20does%2C%20Excel%20sheet%20must%20be%20able%20to%20auto%20correct%20and%20set%20it%20back%20to%20the%20acceptable%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20'Format%20Cells'%20and%20Data%20Validation%20which%20were%20of%20no%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20acceptable%20format%20I'm%20trying%20to%20freeze%20is%20DD%20Month%20YYYY%20(Eg.%2012%20June%202019)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20enlighten%20me%20with%20more%20options%20if%20available%20or%20to%20use%20data%20validation%20in%20excel%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-687350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687743%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687743%22%20slang%3D%22en-US%22%3EThis%20is%20far%20from%20simple%20to%20make%20absolutely%20100%25%20secure%2C%20as%20it%20is%20easy%20to%20break%20a%20conditional%20formatting%20rule%20by%20a%20simple%20copy%20and%20paste.%20%3CBR%20%2F%3EOne%20way%20to%20make%20pasting%20harder%20is%20by%20merging%20two%20cells%20next%20to%20each%20other%20before%20setting%20up%20the%20Validation%20on%20the%20merged%20cells%2C%20unlocking%20the%20merged%20cell%20(and%20setting%20a%20date%20format)%20and%20then%20protecting%20the%20worksheet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687766%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687766%22%20slang%3D%22en-US%22%3EThanks%2C%20firstly%2C%20is%20t.hat%20even%20achievable%20in%20Excel%3F%20I%20mean%20a%20standard%20date%20format%20set%20for%20a%20column%20and%20users%20entering%20data%20whatsoever%20ways%20will%20be%20autocorrected%20to%20the%20fixed%20format.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687852%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687852%22%20slang%3D%22en-US%22%3EAlso%2C%20I%20can%20use%20the%20'Excel%20Date%20Picker%20Add%20on'%20then%20let%20the%20users%20pick%20up%20a%20date%2C%20however%2C%20this%20would%20require%20the%20users%20to%20have%20the%20add%20on%20installed%20on%20their%20systems.%20So%20trying%20to%20figure%20out%20if%20by%20any%20way%20I%20can%20keep%20the%20users%20from%20entering%20data%20in%20a%20wrong%20or%20different%20format.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688356%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688356%22%20slang%3D%22en-US%22%3EMore%20or%20less%2C%20if%20it%20is%20entered%20in%20a%20form%20Excel%20can%20turn%20into%20a%20date%20it%20will%20do%20so%2C%20but%20you%20risk%20Excel%20getting%20it%20wrong%20of%20people%20type%20dates%20in%20the%20wrong%20day%2Fmonth%20order.%20There%20is%20no%20fool-proof%20way%20to%20avoid%20that%20other%20than%20by%20using%20VBA%20I%20think.%20Even%20then%20there%20are%20always%20ways%20around%20it.%20Database%20applications%20are%20much%20better%20equipped%20for%20a%20task%20like%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688370%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20looks%20like%20I'll%20have%20to%20depend%20on%20VBA%20for%20this.%20Any%20idea%20what%20the%20code%20would%20probably%20look%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-689472%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-689472%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358978%22%20target%3D%22_blank%22%3E%40Karthick360%3C%2FA%3E%20Here's%20en%20example%20using%20%3CA%20href%3D%22https%3A%2F%2Fsites.google.com%2Fsite%2Fe90e50%2Fcalendar-control-class%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20code.%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690017%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690017%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%2C%20I%20think%20I%20got%20what%20I%20have%20been%20looking%20for.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698314%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698314%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20used%20the%20following%20URL%20to%20set%20up%20the%20date%20picker%20in%20Excel%2C%20which%20has%20been%20successful.%20However%2C%20I%20couldn't%20get%20the%20required%20format%20which%20is%20mm-dd-yyyy.%20I%20have%20used%20the%20same%20VBA%20code%20as%20in%20the%20URL.%20Could%20someone%20please%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAmallard%2FexcelTricks%2Fblob%2Fmaster%2FDatePickerToCells.md%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAmallard%2FexcelTricks%2Fblob%2Fmaster%2FDatePickerToCells.md%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EKarthick.S%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698552%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358978%22%20target%3D%22_blank%22%3E%40Karthick360%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConditional%20formatting%20appears%20to%20work.%3C%2FP%3E%3CP%3ENumber%20formatting%20set%20as%20a%20conditional%20format%20to%20any%20positive%20number%20greater%20than%2030000%20(say)%20will%20override%20any%20input%20default%20or%20user%20setting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%2C%20until%20someone%20overwrites%20the%20conditional%20format%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699114%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699114%22%20slang%3D%22en-US%22%3EBest%20to%20include%20your%20file%20(scrubbed%20of%20any%20sensitive%20information!)%20so%20we%20can%20see%20what%20you%20tried.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699446%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESure%2C%20here%20we%20go.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699448%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20Thank%20you%2C%20will%20have%20to%20try%20and%20let%20you%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699582%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358978%22%20target%3D%22_blank%22%3E%40Karthick360%3C%2FA%3E%20Change%20the%20code%20to%20match%20the%20code%20below%20and%20format%20your%20cells%20with%20the%20proper%20date%20format.%3C%2FP%3E%0A%3CPRE%3EOption%20Explicit%0A%0APrivate%20Sub%20DTPicker1_Change()%0A%20%20%20%20ActiveCell.Value%20%3D%20CDate(DTPicker1.Value)%0AEnd%20Sub%0A%0APrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%0A%0A%20%20%20%20With%20Sheet1.DTPicker1%0A%20%20%20%20%20%20%20%20.Height%20%3D%2020%0A%20%20%20%20%20%20%20%20.Width%20%3D%2020%0A%20%20%20%20%20%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22A%3AA%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Visible%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20.Top%20%3D%20Target.Top%0A%20%20%20%20%20%20%20%20%20%20%20%20.Left%20%3D%20Target.Offset(0%2C%201).Left%0A%20%20%20%20%20%20%20%20%20%20%20%20.Value%20%3D%20Target.Value%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20.Visible%20%3D%20False%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699604%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%2C%20it%20works!!.%20However%2C%20one%20cell%20alone%20changes%20back%20to%20a%20different%20format%20by%20itself.%20Not%20sure%20why.%20Have%20attached%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699741%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699741%22%20slang%3D%22en-US%22%3EClear%20the%20LinkedCell%20property%20of%20the%20date%20picker.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699771%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699771%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20ton%2C%20it%20worked.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-700458%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-700458%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20will%20this%20work%20when%20I%20send%20the%20sheet%20to%20users%20that%20may%20not%20have%20date%20picker%20control%20installed%20in%20their%20systems%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-705623%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20one%20fixed%20format%20of%20date%20in%20Excel%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-705623%22%20slang%3D%22en-US%22%3ENo%20it%20will%20not.%20Hence%20my%20proposition%20to%20use%20that%20other%20one.%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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

18 Replies
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.
Thanks, firstly, is t.hat even achievable in Excel? I mean a standard date format set for a column and users entering data whatsoever ways will be autocorrected to the fixed format.
Also, I can use the 'Excel Date Picker Add on' then let the users pick up a date, however, this would require the users to have the add on installed on their systems. So trying to figure out if by any way I can keep the users from entering data in a wrong or different format.
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.

@Jan Karel Pieterse 

 

So it looks like I'll have to depend on VBA for this. Any idea what the code would probably look like?

Solution

@Deleted Here's en example using this code.

Thank you so much, I think I got what I have been looking for.

I have used the following URL to set up the date picker in Excel, which has been successful. However, I couldn't get the required format which is mm-dd-yyyy. I have used the same VBA code as in the URL. Could someone please help?

 

https://github.com/Amallard/excelTricks/blob/master/DatePickerToCells.md

 

Thanks,

Karthick.S

@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.

Best to include your file (scrubbed of any sensitive information!) so we can see what you tried.

@Peter Bartholomew  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

@Jan Karel Pieterse 

 

Thank you so much, it works!!. However, one cell alone changes back to a different format by itself. Not sure why. Have attached the sheet.

Clear the LinkedCell property of the date picker.

@Jan Karel Pieterse 

 

Thanks a ton, it worked. 

By the way, will this work when I send the sheet to users that may not have date picker control installed in their systems?

Highlighted
No it will not. Hence my proposition to use that other one.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies