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

Aaron DOJ

New Member
Joined
Aug 10, 2018
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
Code:
Sub Format_Dates()
    Selection.NumberFormat = "mm/dd/yy"
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
Code:
[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

Code:
Selection.NumberFormat = "mm/dd/yy"

Code:
[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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
Great idea, however, after putting that in, the coding changes it back to just MM/DD/YY
 
Upvote 0
Same principle

Code:
 .NumberFormat = "- mm/dd/yy"
 
Upvote 0
The issue with that is it puts the dash in every cell, how can I focus just that format to 1?
 
Upvote 0
Is it always the same cell?
If not, how is it determined?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top