How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)

In this article, I’ll show you how you can use the time format in VBA in Excel. You’ll learn to display the time, as well as the date, in any desired format with proper examples and illustrations.

How to Use Time Format in Excel VBA (Quick View)

Sub Current_Time()
Format_Date = InputBox("Enter Your Desired Format: ")
MsgBox Format(Now(), Format_Date)
End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Use Time Format in Excel VBA

To use the time format in Excel VBA, you’ll have to learn to use all the formats available to display a complete time.

Here I am showing you the formats available for all the sections of a time display one by one.

⧭ Month Format:

There are 4 formats available to use for the month.

⧭ Day Format:

There are also 4 formats available to use for the day.

⧭ Year Format:

There are 2 formats available to use for the year.

⧭ Hour Format:

There are 2 formats available to use for the hour.

⧭ Minute Format:

There are also 2 formats available to use for the minute.

⧭ Second Format:

There are also 2 formats available to use for the seconds.

⧭  AM/PM Format:

There is only 1 format available to use for AM/PM.

Examples to Use Time Format in Excel VBA

We have seen all the formats that we can use in Excel VBA to display a date or time. Now, let’s explore a few examples to understand the use of time format in VBA clearly.

1. Developing a Macro to Display Time in Desired Format in Excel VBA

First of all, we’ll develop a Macro to display the current date and time in our desired format.

First, we’ll get the current time using the Now function of VBA.

Next, we’ll display the time in our desired format using the Format function of VBA.

The VBA code will be:

⧭  VBA Code:

Sub Current_Time()

Format_Date = InputBox("Enter Your Desired Format: ")

MsgBox Format(Now(), Format_Date)

End Sub

⧭  Output:

Run the code. An input box will ask you to enter the format in which you want to display the time.

Here I’ve used the format mm/dd/yyyy hh:mm:ss

Then click OK. It’ll display the current date and time in your desired format.

Read More: How to Calculate Difference Between Two Dates and Times in Excel

2. Creating a User Defined Function to Get Time in Desired Format in Excel VBA

We’ve created a Macro to display the current date. Now, we’ll create a User-Defined function to display the current date in our desired format.

We’ll follow the same procedure as described above. We’ll get the current date and time by the Now function, then convert it to the desired format by the Format function.

The VBA code will be:

⧭  VBA Code:

Function CurrentTime(Format_Date)

CurrentTime = Format(Now(), Format_Date)

End Function

⧭  Output:

Run this function in any cell of the worksheet with your desired format as the argument.

It’ll display the current date and time in the mentioned format.

Read More: How to Calculate the Duration of Time in Excel (7 Methods)

Similar Readings

• Calculate Elapsed Time Between Two Dates in Excel (5 Methods)
• How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
• Excel Formula for Overtime over 8 Hours (4 Examples)
• How to Calculate Average Response Time in Excel (4 Methods)
• Add Hours to Time in Excel (8 Quick Ways)

3. Developing a UserForm to Get Time in Desired Format in Excel VBA

Finally, we’ll develop a UserForm to get the current date and time in our desired format.

⧪ Step 1: Inserting the UserForm

Go to the Insert > UserForm option in the VBA toolbar to insert a new UserForm.

⧪ Step 2: Dragging Tools to the UserForm

A UserForm called UserForm1 will be opened, along with a Toolbox called Control.

Move your mouse over the Toolbox and drag two Labels (Label1 and Label2) in the UserForm. Change the Labels of the UserForm to Choose Format: and Current Time: respectively.

Similarly, drag a ListBox (ListBox1) below Label1 and a TextBox (TextBox1) below Label2.

⧪ Step 3: Writing Code for ListBox1

Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Enter the following code there.

Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1

If UserForm1.ListBox1.Selected(i) = True Then

UserForm1.TextBox1.Text = Format(Now(), UserForm1.ListBox1.List(i))

End If

Next i

End Sub

⧪ Step 4: Writing Code for Running the UserForm

Now is the final step. Insert a new Module from the VBA toolbar and insert the following code.

Sub Run_UserForm()

UserForm1.Caption = "Display Time"

UserForm1.ListBox1.AddItem "mm/dd/yy"

UserForm1.ListBox1.AddItem "mmm/dd/yy"

UserForm1.ListBox1.AddItem "mm/dd/yyyy"

UserForm1.ListBox1.AddItem "mmm/dd/yyyy"

UserForm1.ListBox1.AddItem "mmm/dd/yy hh:mm:ss"

UserForm1.ListBox1.AddItem "mmm/dd/yy hh:mm:ss"

UserForm1.ListBox1.AddItem "mmm/dd/yyyy hh:mm:ss"

UserForm1.ListBox1.AddItem "mm/dd/yyyy hh:mm:ss"

UserForm1.ListBox1.AddItem "mmm/dd/yyyy hh:mm:ss"

UserForm1.ListBox1.ListStyle = fmListStyleOption

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle

Load UserForm1

UserForm1.Show

End Sub

⧪ Step 5: Running the UserForm (The Final Output!)

Your UserForm is now ready to use. Run the Macro called Run_UserForm. It’ll display the UserForm with a ListBox containing all the formats available.

Select any of the available formats from the ListBox. It’ll display the current date in the selected format.

Read More: How to Calculate Time Difference in Excel (13 Ways)

Things to Remember

Here we’ve shown to display the current date with the Now function of VBA. But obviously, you can display any other date with the Date function of VBA.

Conclusion

So, these are the ways to use the time format with VBA in Excel. Hope the examples involving the Macro, UDF, and UserForm will make things clear for you. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Calculate Turnaround Time in Excel (4 Ways)
• Calculate Hourly Rate in Excel (2 Quick Methods)
• How to Subtract and Display Negative Time in Excel (3 Methods)
• Subtract Military Time in Excel (3 Methods)
• How to Subtract Date and Time in Excel (6 Easy Ways)
• How to Add Time in Excel Over 24 Hours (4 ways)
• Calculate Production per Hour in Excel (4 Ways)
• How to Calculate Average Handling Time in Excel (2 Easy Ways)