Forum Discussion

Deleted's avatar
Deleted
Jun 12, 2019
Solved

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 ab...
excel
Excel Online
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 17, 2019

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.

  • Deleted's avatar
    Deleted
    Jun 18, 2019

    PeterBartholomew1  Thank you, will have to try and let you know.

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jun 18, 2019

      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
      • Deleted's avatar
        Deleted
        Jun 18, 2019

        JKPieterse 

         

        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.

Resources