I'm setting up a userform for employees to fill in data. They need to fill in the date like DD-MM-YYYY the output must also be DD-MM-YYYY. However the code I use changes input 5-12-2019 (DD-MM-YYYY), to output 12-5-2019 (MM-DD-YYYY). In the userform it stays like (DD-MM-YYYY) expect for when you tranfer it to your excel sheet. If the month is above 13 it doesn't change, so input 13-12-2019 (DD-MM-YYYY) stays output 13-12-2019 (DD-MM-YYYY). How is it possible that the code changes the output but not everytime the same way, did I do something wrong in the code?

Private Sub TextBox1_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If IsDate(Me.TextBox1.Text) Then
        Me.TextBox1.Text = Format(Me.TextBox1.Text, "DD-MM-YYYY")
        MsgBox "Vul een geldige datum in", vbRetryCancel + vbCritical
    End If

End Sub

Public Function IsTime(Expression As Variant) As Boolean
    If IsDate(Expression) Then
        IsTime = (Int(CSng(CDate(Expression))) = 0)
    End If
End Function

1 Answers

Vityata On
  1. Read the date as a string. E.g. 5-12-2009.
  2. Split the string by -.
  3. The 0th value is the day, the 1st is the month, the 2nd is the year.
  4. From there, you have a fully functioning date, using DateSerial().

Public Function StringToDate(myInput As String) As Date

    Dim day As Long
    Dim month As Long
    Dim year As Long
    Dim dateArray As Variant

    dateArray = Split(myInput, "-")
    day = dateArray(0)
    month = dateArray(1)
    year = dateArray(2)

    StringToDate = DateSerial(year, month, day)

End Function

Public Sub Main()

    Debug.Print month(StringToDate("05-10-2001"))
    Debug.Print StringToDate("05-10-2001")

End Sub