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

Quick View to Use Time Format in Excel VBA


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.

Characters Description Example
m Number in 1 digit. 1
mm Number in 2 digits. 01
mmm Short name of the month. Jan
mmmm Full name of the month. January

⧭ Day Format:

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

Characters Description Example
d Number in 1 digit. 1
dd Number in 2 digits. 01
ddd Short name of the day. Sun
dddd Full name of the day. Sunday

⧭ Year Format:

There are 2 formats available to use for the year.

Characters Description Example
yy Last 2 digits of the year. 22
yyyy Full form of the year. 2022

⧭ Hour Format:

There are 2 formats available to use for the hour.

Characters Description Example
h Hour in 1 digit. 7
hh Hour in 2 digits. 07

⧭ Minute Format:

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

Characters Description Example
m Minute in 1 digit. 6
mm Minute in 2 digits. 06

⧭ Second Format:

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

Characters Description Example
s Second in 1 digit. 9
ss Second in 2 digits. 09

⧭  AM/PM Format:

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

Characters Description Example
AM / PM Displays AM / PM. 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

Quick View to Use Time Format in Excel VBA

⧭  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

Output to Use Time Format in Excel VBA

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

User Defined Function to Use Time Format in Excel VBA

⧭  Output:

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

=CurrentTime("mm/dd/yyyy hh:mm:ss")

Function to Use Time Format in Excel VBA

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


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.

Inserting UserForm to Use Time Format in Excel VBA

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.

Dragging Tools to Use Time Format in Excel VBA

⧪ 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

VBA Code to Use Time Format in Excel VBA

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

Running UserForm to Use Time Format in Excel VBA

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo