Wanting all cells that are supposed to be dates to be in MM/DD/YY format

[Aaron DOJ]

I am trying to get just the date fields in my spreadsheet to always be MM/DD/YY format no matter what kind of date is entered such as 1/1 will come out 01/01/18 or 1 jan will change to 01/01/18 etc. For the most part I got it to work with the codes below, the problem I am now encountering, that I can't seem to think how to stop is any other field with numbers or text from changing to a date format...can someone help me please

Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Cells(2, 2)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(2, 2).Value = "MM/DD/YY"
    Cells(2, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(2, 2).Value = "MM/DD/YY" Then 'if equal to "Value", change text to teal.
    Cells(2, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(2, 2).Value <> "MM/DD/YY" Then 'if text other than "Value", change text to black.
    Call Format_Dates
    Cells(2, 2).Font.ColorIndex = 1
End If
   
If IsEmpty(Cells(3, 2)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(3, 2).Value = "MM/DD/YY"
    Cells(3, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(3, 2).Value = "MM/DD/YY" Then 'if equal to "Value", change text to teal.
    Cells(3, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(3, 2).Value <> "MM/DD/YY" Then 'if text other than "Value", change text to black.
    Call Format_Dates
    Cells(3, 2).Font.ColorIndex = 1
End If
   
End Sub

Module

Sub Format_Dates()
    Selection.NumberFormat = "mm/dd/yy"
End Sub

 

[Yongle]

1. The line below from your code points at selected cells (ie ones selected in your worksheet) whereas what you actually want is for the cells mentioned in your code to be formatted

[COLOR=#ff0000]Selection[/COLOR].NumberFormat = "mm/dd/yy"

2. Your code is repeating formatting lines unnecessarily

3. Alternative VBA
- DateFormatCell does the formatting
- Selection_Change passes the range to be formatted as it calls DateFormatCell

Selection.NumberFormat = "mm/dd/yy"

[B]Private Sub Worksheet_SelectionChang[/B]e(ByVal Target As Range)
[COLOR=#0000ff][I]'format one cell at a time[/I][/COLOR]
    DateFormatCell Cells(2, 2)
    DateFormatCell Cells(3, 2)
[COLOR=#0000ff][I]'format a list of cells[/I][/COLOR]
    DateFormatCell Range("B4, B6, B8, B10")
[COLOR=#0000ff][I]'format a contiguous range of cells[/I][/COLOR]
    DateFormatCell Range("C10:C25")
End Sub

Private Sub [B]DateFormatCell[/B](ByRef rng As Range)
    Dim cel As Range
    For Each cel In rng
        With cel
            If IsEmpty(cel) Then 'if empty, put in "Value" and make value change text to teal.
                .Value = "MM/DD/YY"
                .Font.ColorIndex = 14
            ElseIf .Value = "MM/DD/YY" Then 'if equal to "Value", change text to teal.
                .Font.ColorIndex = 14
            ElseIf .Value <> "MM/DD/YY" Then 'if text other than "Value", change text to black.
                .NumberFormat = "mm/dd/yy"
                .Font.ColorIndex = 1
            End If
        End With
    Next cel
End Sub

 

[Aaron DOJ]

Yongle,

Sorry for the delay in my reply, it was the weekend and I didn't have access. Thank you for assisting me in figuring this issue out, I believe it is working as I hoped, here is a more complicated part (or so I feel). I have many fields that need individual dates and that is what was more important than anything (what you had helped me solve ), but I also have another section that is to tell me the date range, for example this form could cover 01/31/18-02/02/18. Do you think you can help me to format that cell (X11) in which I would love to force the user to put in MM/DD/YY-MM/DD/YY (01/31/18-02/02/18)

-Aaron

 

[Yongle]

Make the user enter From date in one cell and the To date in another cell?
2 values, 2 cells - easy. Excel can deal with that "out of the box"- why make life difficult?

 

[Aaron DOJ]

Fair enough, I was hoping to avoid that as it takes some semi-major restructuring of cells, to unmerge cells, add a column to add in "-" and then fix all of the other columns/cells/coding, but it would be the easiest way i suppose. Then I can put in the same code as you laid out above. Or I could leave out the "-" unmerge the cell add a bit of code, im just not sure how visually appealing that is not having that dash.

 

[Yongle]

im just not sure how visually appealing that is not having that dash

Format your 2nd date cell to include the dash
right-click cell \ format cells \ number \ custom \ enter this under Type - dd/mm/yy
Now enter a 15 Sep 2018 in the cell. It returns - 15/09/18 (It is a display format not a negative number)

 

[Aaron DOJ]

Great idea, however, after putting that in, the coding changes it back to just MM/DD/YY

 

[Yongle]

Same principle

 .NumberFormat = "- mm/dd/yy"

 

[Aaron DOJ]

The issue with that is it puts the dash in every cell, how can I focus just that format to 1?

 

[Yongle]

Is it always the same cell?
If not, how is it determined?