Excel VBA: InputBox Date Format

Changing data from random format to date format is a very important task sometimes when we work in Excel. It’s quite easy to change the format of data from the Excel ribbon. But we can also use small pieces of VBA code that suffice to do the task.In this article, we will show several methods to change data from InputBox in a random format to a date format with the help of VBA code in Excel. For example, we will show how to validate date-type data from InputBox, and how to convert random data to date format with several VBA functions.

In addition, we will show the way to change the date format, and the procedures to display time with a date. And we will only use VBA code for performing all the tasks.

This video demonstrates the method to use VBA Format function to convert a date( taken by InputBox)  in random format to a proper date format.

How to Launch VBA Editor in Excel

For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut Alt + F11.

If your Excel doesn’t have the Developer tab automatically, then you can enable Developer tab in Excel.

After opening the VBA window, you need to insert a new Module.

For running any code, just press the Run button from the VBA window, as in the screenshot. Also, you can press the keyboard shortcut F5.

Excel VBA: InputBox Date Format (6 Examples)

In this section, we will show 6 methods to perform different actions with data from InputBox to convert them to date format. Without any delay, let’s jump to the procedures.

1. Using VBA IsDate and CDate Functions for Date Format Validation

In the first method, we are showing how to take date-type data in any format with InputBox, convert it to date format, and show it with MsgBox.

Just 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.

Read More: Excel VBA: InputBox Type with Examples

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

In this method, we will show how to input a valid date from InputBox to our worksheet. The method is very simple.

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.

Read More: Excel VBA InputBox with Number Only Criteria

3. Converting InputBox Data to Date Utilizing VBA Format Function

Now, we will show the 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.

Read More: Excel VBA: InputBox with Default Value

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. That’s 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

In the code, we have 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.

Read More: Excel VBA: Custom Input Box

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

This time, we will 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.

Read More: VBA InputBox for Integer Value Only in Excel

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

This method is a simpler way to show dates. The method is quite similar to the previous method.

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 this code, In the code, we took the day, month, and year values of a date separately with the InputBox and then, 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

Now, we will take a date in any format, separate its day, month, and year values, and rearrange them in mm/dd/yyyy format. Let’s see the method.

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

Here, we took an 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.

Read More: Vbscript InputBox with Multiple Fields in Excel

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.

Wrapping Up

• In the first method, we showed how to take a date as input with InputBox, validate it, then show it with MsgBox.
• Then, we discussed the way to include the valid date on the worksheet.
• Also, we showed methods to convert data from a random format to a date format with VBA Format & DateValue
• Later methods discussed dealing with the date components and making a proper date with them.
• In addition, we have discussed how to change the date format, also the way to display time with date.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, we covered a wide range of topics related to converting data from InputBox to date format. Hopefully, it will come in handy for you, and you can solve your related problems now. If you have any queries or suggestions, feel free to leave a comment in the comment section.