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.
Download Practice Workbook
You can download the practice workbook from here.
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.
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
- 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: How to Use Excel VBA InputBox with Example
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.
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
- 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.
Apply the attached code in a new module of the VBA window and run it.
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
- 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: Vbscript InputBox with Multiple Fields in Excel (2 Examples)
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.
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: InputBox Type with Examples
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.
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.
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.
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.
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.
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
- 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: Excel VBA: Custom Input Box (7 Examples)
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.
- 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.
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. Visit our ExcelDemy website for more articles about Excel.