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

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

Highlighted
Solution

@Deleted Here's en example using this code.

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

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

Highlighted

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

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

@Peter Bartholomew  Thank you, will have to try and let you know.

Highlighted

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

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

Highlighted
Clear the LinkedCell property of the date picker.
Highlighted

@Jan Karel Pieterse 

 

Thanks a ton, it worked. 

Highlighted

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
Adding a line onto a pre-designed Gantt chart
hoffmanwolff in Excel on
0 Replies
Getting #Value!
ranjit995 in Excel on
2 Replies
pratica 1002380750 - query web
Ben_Hur in Excel on
1 Replies
Custom Sheet View is not working in Office 365
RonanK in Excel on
1 Replies
help with macris & VBA
Adil_813 in Excel on
0 Replies
How to count average in certain time range
rutilusss in Excel on
1 Replies