Excel form: Input random date in form comes out all screwy

Question

In excel 2010 I am creating a VERY simple form. I have a text box in which I ask a user to input a date. This text box is named startDate

I test it with a date input in the following format: dd/mm/yyyy

then in the VBA editor I declare it as a date:

Dim startDate As Date

I next have a message box report back the start date just to make sure its working: msgBox (startDate)

I input 1/12/1983 and the msgBox reports back 12:00 am

Why????

Any help would be appreciated.

Automate This Automate This · Accepted Answer · 2014-06-30T03:58:31

Try this:

MsgBox FormatDateTime(TheDate, vbShortDate)

You can also set the input format like this:

Sub inputDate()
    Dim dateString As String, TheDate As Date
    Dim valid As Boolean: valid = True

    Do
      dateString = Application.InputBox("Enter A Start Date (dd/mm/yy): ", Format(Now(), "dd/mm/yy"))

      If IsDate(dateString) Then
        TheDate = DateValue(dateString)
        valid = True
      Else
        MsgBox "Invalid date"
        valid = False
      End If
    Loop Until valid = True

    If valid Then MsgBox (TheDate)
End Sub

Excel form: Input random date in form comes out all screwy

1 Answers