Excel VBA: InputBox Date Format

Get FREE Advanced Excel Exercises with Solutions!

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.

Opening the VBA window from the Developer tab.

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.

Opening new module in the VBA window

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.

Step to run vba code


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.

Code for Date Format Validation Using VBA IsDate and CDate Functions

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

Code Breakdown:

  • Firstly, we created a sub-procedures named Date_Validation and declared the variable string_Date as a string.
  • Then, we have given a value to the string_Date variable with the help of InputBox of VBA.
  • After that, we checked whether the value of string_Date is a date or not with the IsDate function and changed its format to dd/mm/yyyy with help of the Format
  • The CDate function inside the Format function gives the date value of string_Date.
  • Lastly, we showed the result data in date format with MsgBox.
  • For any other input value rather than date, the MsgBox will show an invalid notification.

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.

Code for Getting a Valid Date from InputBox and Put It on the 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

Code Breakdown:

  • In the code, we created a while loop, the loop will go on until we give a valid date to InputBox. For each invalid date or empty input, the code will show an invalid notification with MsgBox. The IsDate and IsEmpty functions help to determine a valid date.
  • Lastly, for any valid date input, we have put the value in Cell C5 of the Date_Worksheet Also, changed its format to dd/mm/yyyy.

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.

Code for Converting InputBox Data to Date Utilizing VBA Format Function

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

Code Breakdown:

  • Firstly, we declared the inputRange variable as range and took the range input with InputBox.
  • Then, we checked the value from InputBox with the IsDate function, if it’s a date value then the Format function changes the format of the value to dd/mm/yyyy. Also, we printed those formatted values in the next cell of the respective date value cell with the help of Offset property. The Offset property helps to navigate any cell relative to the active cell. For example, if our active cell is B5 then Cell.Offset(0,1) will denote cell C5.

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.

Code for Using DateValue Function to Convert InputBox Data to Date

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.

Code for Applying the DateSerial Function to Construct a Date from Year, Month, and Day in Valid Date Format

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.

Code for Inserting Day, Month, and Year Components from InputBox and Displaying with MsgBox

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.

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

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.

Code to Convert Date Format from dd/mm/yyyy to mm/dd/yyyy in Excel 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

Code Breakdown:

  • Firstly, we entered date and time values separately with two InputBox.
  • Then, checked if the values are correct with the help of the IsDate
  • Lastly, we join the date and time parts together with “&” and put the value in cell C4.
  • Finally, we changed the format of cell C4 to “dd-mm-yy  h:mm AM/PM” which is a custom format to display both date and time.

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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo