Now and Format Functions in Excel VBA (4 Examples)

Visual Basic for Applications (VBA) is one of the important parts of Microsoft Excel. VBA allows users to create customized functions and applications rather than those available in Excel. VBA contains lots of functions like Excel default functions. The Now and Format functions are among them. We will discuss the applications of Now and Format functions in Excel VBA.

VBA Now Function Overview

Function Objective:

This Now function returns the present date and time according to the user’s computer’s default date and time.

Syntax:

=Now()

Returns:

Returns date and time get from the computer’s system.

VBA Format Function Overview

Function Objective:

This Format Function returns a string according to the format given on the code.

Syntax:

=Format(Expression, [ Format ], [ FirstDayOfWeek ], [ FirstWeekOfYear ])

Argument:

Settings:

The firstdayofweek has the following settings:

The setting of the firstweekofyear argument is below:

Returns: 

Returns input value according to the specified format.

Format Symbols:

We have two types of format symbols used in the function. Date and Time symbols

Date Symbols:

Time Symbol:

We will show some examples using the Now and Format function in Excel VBA.

1. Using NOW Function in Excel VBA

In this example, we will show the use of the Now function in VBA.

Step 1:

• First, go to the Developer tab.
• Then click Record Macro.

Step 2:

• Put Test1 as the Macro name.
• Then press OK.

Step 3:

• Now, write the below code on the command module.

Sub Test1()
Dim Msg
Msg = Now()
MsgBox Msg
End Sub

Step 4:

• Now, press F5 to run the code.

Here, we applied the Now function and the date and time of the present time with date.

2. Using FORMAT Function in VBA

In this example, we will show the use of the Format function in VBA.

Step 1:

• Create a new macro named Test2.

Step 2:

• Now put the below code containing the Format

Sub Test2()
Dim Msg
Dim date1 As Date
date1 = #1/27/1993#
Msg = Format(date1, "Long Date")
MsgBox Msg
End Sub

Step 3:

• Then press F5 and run the code.

In this example, we get the date according to the format mentioned in the code.

Read More: Excel VBA: Insert Timestamp When a Macro Is Run

3. Combining NOW and FORMAT Function in VBA

In this section, we will combine the Now and Format functions in VBA. The Format function will be applied to the data obtained from the Now function.

Step 1:

• Create a macro for this example named Test3.

Step 2:

• Now, put the below code on the command module.

Sub Test3()
Dim Msg
Dim date1 As Date
date1 = Now()
Msg = Format(date1, "mmmm dd, yyyy hh:mm AM/PM")
MsgBox Msg
End Sub

Step 3:

• Run the code by pressing the F5 button.

Finally, we are getting the present date and time according to our specified format.

4. Formatting Current Time in Different Ways using NOW and FORMAT Function

Here, we will present the Now function in different ways using the Format function.

Step 1:

• Named the new macro as Test4.

Step 2:

• Write the below code on the command module.

Sub Test4()
Range("C4") = Format(Now(), "Long Time")
End Sub

Step 3:

• Press the F5 button to run the code.

Here we get the result in the long-time format.

Now, we will represent in more ways.

Step 4:

• Here, we showed 9 examples combination of Now and Format functions in the below code.

Sub Test4()
Range("C4") = Format(Now(), "dd mmmm yy")
Range("C5") = Format(Now(), "ddd dd")
Range("C6") = Format(Now(), "mmmm yy")
Range("C7") = Format(Now(), "dd.mm.yyyy hh:mm")
Range("C8") = Format(Now(), "dd.mm.yyyy hh:mm Am/PM")
Range("C9") = Format(Now(), "Long Time")
Range("C10") = Format(Now(), "hh:mm:ss AM/PM")
Range("C11") = Format(Now(), "n:ss")
Range("C12") = Format(Now(), "w,ww", 7, vbFirstJan1)
End Sub

Step 5:

• After pressing F5 we get the below result.

We have other options to present the Now function. According to the user’s needs, they can modify them.

Things to Remember

The date value will be shown according to the computer’s system default calendar. Like Gregorian, Hijri etc.

Download Practice Workbook

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

Conclusion

In this article, we showed some examples of the Now and Format function in Excel VBA. I hope this will satisfy your needs. If you have any questions or suggestions regarding this, please feel free to share these in the comment section below or on our forum.