Excel VBA: InputBox Date Format: 6 Methods

Method 1 – Using VBA IsDate and CDate Functions for Date Format Validation

Show how to take date-type data in any format with InputBox, convert it to date format, and show it with MsgBox.

Include the following code in a new module of the VBA window and run it.

Code:

Public Sub Date_Validation()
Dim string_Date As String
'date insertion by user
string_Date = InputBox("Insert Date in format dd/mm/yyyy or mm/dd/yyyy", _
"Date Inputbox", Format(Now(), "dd/mm/yyyy"))
'date validation & output show
If IsDate(string_Date) Then
string_Date = Format(CDate(string_Date), "dd/mm/yyyy")
MsgBox string_Date & " This is a Valid Date"
Else
MsgBox "Date Format is Invalid"
End If
End Sub

Check out the video for better clarification of the method.

Method 2 – Getting a Valid Date from InputBox and Insert It on the Worksheet

How to input a valid date from InputBox to our worksheet.

Write the attached code in a new module of the VBA window and run it.

Code:

Sub Dates_In_Worksheet()
Dim date_Input As Variant
Dim date_InputOK As Boolean
'taking non-empty date from user
date_InputOK = False
While Not date_InputOK
date_Input = InputBox("Enter A Date", "Dates Only")
If IsEmpty(date_Input) Or IsDate(date_Input) Then
date_InputOK = True
Else
'showing alert message if user input invalid date or empty value
Call MsgBox("Need a real date", vbCritical + vbOKOnly, "Dates Only")
End If
Wend
'including valid date on worksheet and changing the cell format
If Not IsEmpty(date_Input) Then
Worksheets("Date_Worksheet").Range("C5").NumberFormat = "mm/dd/yyyy"
Worksheets("Date_Worksheet").Range("C5").Value = date_Input
End If
End Sub

Check out the video for better clarification of the method.

Mtehod 3 – Converting InputBox Data to Date Utilizing VBA Format Function

Use of the VBA Format function to change the data from InputBox to date format.

Apply the attached code in a new module of the VBA window and run it.

Code:

Sub Data_to_Date_Format()
Dim inputRange As Range
Dim cell As Range
' Prompt user for input range
Set inputRange = Application.InputBox("Enter cell range:", , , , , , , 8)
' Loop through each cell in the input range and format its value as date
For Each cell In inputRange
If IsDate(cell.Value) Then
cell.Offset(0, 1).Value = Format(cell.Value, "dd/mm/yyyy")
End If
Next cell
End Sub

Check out the video for a better understanding of the method.

Method 4 – Using DateValue Function to Convert InputBox Data to Date

Another way to change data of random type to date format is by using the VBA DateValue function. We are showing the method below.

Insert the following code in a new module of the VBA window and run it.

Code:

Sub Data_to_Date_Format()
Dim inputRange As Range
Dim cell As Range
' Prompt user for input range
Set inputRange = Application.InputBox("Enter cell range:", , , , , , , 8)
' Loop through each cell in the input range and format its value as date
For Each cell In inputRange
If IsDate(cell.Value) Then
cell.Offset(0, 1).Value = DateValue(cell.Value)
End If
Next cell
End Sub

We used the VBA DateValue function, which works just like the VBA Format function, except its format is predefined as the date.

Check out the video for a better understanding of the method.

Method 5 – Applying DateSerial Function to Construct a Date from Year, Month, and Day in Valid Date Format

Take day, month, and year values as input with InputBox, add them, and show the result date with the MsgBox.

Apply the attached code and run it for this method to work.

Code:

Sub Apply_DateSerial()
Dim yearInput As String
Dim monthInput As String
Dim dayInput As String
Dim formattedDate As Date
' Prompt user for year, month, and day input
yearInput = InputBox("Enter year:", "Date Input")
monthInput = InputBox("Enter month:", "Date Input")
dayInput = InputBox("Enter day:", "Date Input")
' Construct a date from the year, month, and day input
formattedDate = DateSerial(yearInput, monthInput, dayInput)
' Do something with the formatted date
MsgBox "You entered the date: " & formattedDate
End Sub

In the above code, we took the day, month, and year values of a date separately with InputBox and then joined them with the DateSerial function and showed the result with the MsgBox.

Follow the video for a better understanding of the method.

Method 6 – Inserting Day, Month, and Year Components from InputBox and Displaying with MsgBox

This method is quite similar to the previous one.

Insert the following code in a new module of the VBA window and run it.

Code:

Sub Get_Date_Component()
Dim yearInput As String
Dim monthInput As String
Dim dayInput As String
Dim formattedDate As Date
' Prompt user for year, month, and day input
dayInput = InputBox("Enter day:", "Date Input")
monthInput = InputBox("Enter month:", "Date Input")
yearInput = InputBox("Enter year:", "Date Input")
' Show all date components together in MsgBox
MsgBox "You entered the date: " & dayInput & "/" & monthInput & "/" & yearInput
End Sub

In the code, we took the day, month, and year values of a date separately with the InputBox joined them with “&” and showed the result with the MsgBox.

Watch the video for a better understanding

How to Convert Date Format from dd/mm/yyyy to mm/dd/yyyy in Excel VBA

Take a date in any format, separate its day, month, and year values, and rearrange them in mm/dd/yyyy format.

Copy the attached code into a new VBA module and run it.

Code:

Sub Converting_Date_Format()
Dim userInput As String
Dim formattedDate As Date
Dim formattedYear As Integer
Dim formattedMonth As Integer
Dim formattedDay As Integer
' Prompt user for date input in "dd/mm/yyyy" format
userInput = InputBox("Enter date in dd/mm/yyyy format:", "Date Input")
' Validate user input with IsDate function
If IsDate(userInput) Then
' Convert user input to a date
formattedDate = CDate(userInput)
' Extract year, month, and day components from the date
formattedYear = Year(formattedDate)
formattedMonth = Month(formattedDate)
formattedDay = Day(formattedDate)
' Do something with the formatted date components
MsgBox "You entered the date: " & formattedMonth & "/" & formattedDay & "/" & _
formattedYear
Else
' Error message for invalid input
MsgBox "Invalid date format. Please enter date in dd/mm/yyyy format."
End If
End Sub

Input date with InputBox. Then we split its day, month, and year value with the Day, Month, and Year VBA functions. Lastly, we joined the separated values with “&” in a new order mm/dd/yyyy, and displayed the result with the MsgBox.

For better understanding, follow the video.

How to Display Date-Time with InputBox in Excel VBA

In addition, to displaying only the date value, let’s see how to display the time value with VBA.

Follow the attached code in a new VBA module and run it.

Code:

Sub Date_Time()
Dim Time As Variant
Dim Working_Day As Variant
'Entering date and time with InputBox
Working_Day = InputBox("Enter your working date", "Enter date")
Do
Time = InputBox("Your Shift for the date", "Enter Time")
'Checking if the date and time are valid
If StrPtr(Time) = 0 Then Exit Sub
If IsDate(Time) Then If IsDate(TimeValue(Time)) Then Exit Do
Loop
'Pasting Date and time on the worksheet and changing the cell format
Worksheets("Date_Time").Range("C4").Value = TimeValue(Time) & Working_Day
Worksheets("Date_Time").Range("C4").NumberFormat = "dd-mm-yy  h:mm AM/PM"
End Sub

For better clarification, take a look at the video.

Frequently Asked Questions (FAQs)

1. What is an InputBox in Excel VBA?

An InputBox is a built-in VBA function that helps the user to input data into a cell or a variable. It is commonly used to gather user input which can be processed by VBA code later on.

2. Can I use an InputBox to prompt the user for a date in Excel VBA?

Of course, you can use the InputBox function with the vbDate option to prompt the user for a date input.

3. What date format does the InputBox function use in Excel VBA?

The InputBox function in Excel VBA uses the date format specified by the user’s system settings which means the format can vary depending on the user’s location and also on settings.

4. How can I specify a specific date format for the InputBox function in Excel VBA?

You can take the help of the Format function to specify a specific date format for the InputBox function.

Things to Remember

• In the second method (Sub Dates_In_Worksheet), we used the While Not loop, which will continue until we input a proper date in the InputBox.
• Don’t forget to save the file in the xlsm format after writing a VBA
• In a few methods, we used cell reference of the worksheet, you have to change it according to your necessity.

Download Practice Workbook

You can download the practice workbook from here.