Date changes dd/mm/yy to mm/dd/yy

[ipbr21054]

Hi,
I have a column formatted to short date.
Today when i enter 01/08/2017 into the first cell it keeps changing to 08/01/2017
If i format the cell to text its fine.

Any reason you know of for this to happen ??

 

[Biz]

Y not enter dates in dd/mm/yyyy and select range of dates and run code below?

Does it help?

Public Sub DateConverter()
    Dim rngCells  As Range
    Dim rngCell   As Range
    Dim strMsg    As String
    Dim intDay    As Integer
    Dim intMonth  As Integer
    Dim intYear   As Integer
    Dim DateValue As Date

    On Error Resume Next
    strMsg = "Select the cells to convert:" & _
                  vbCr & vbCr & "Reverses Month and Day date evaluation," _
                  & vbCr & "i.e. DD-MM becomes MM-DD if possible."
    'Receive the input.
    Set rngCells = Application.InputBox(strMsg, "Date Converter", , , , , , 8)
    'Test for no input received.
    If Not IsObject(rngCells) Or rngCells Is Nothing Then
       GoTo Exit_DateConverter
    End If
    
    'Date Conversion Loop.
    For Each rngCell In rngCells
        If IsDate(rngCell) Then
            intDay = Day(rngCell)
            intMonth = Month(rngCell)
            intYear = Year(rngCell)
            DateValue = intMonth & "/" & intDay & "/" & intYear
            rngCell.Value = DateValue
        End If
    Next rngCell
    
    'Format Date
    rngCells.NumberFormat = "mm/dd/yy"
Exit_DateConverter:
End Sub