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 customize functions and applications rather than those are available in Excel. VBA contains lots of functions like Excel default functions. Now and Format functions are among them. We will discuss the applications of Now and Format functions in Excel VBA.


Download Practice Workbook

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


Introduction to VBA Now Function

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.


Introduction to VBA Format Function

Function Objective:

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

Syntax:

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

Argument:

ARGUMENT REQUIRED/OPTIONAL EXPLANATION
Expression REQUIRED One correct expression.
Format OPTIONAL The expected format is defined by the user.
FirstDayOfWeek OPTIONAL It specifies the first day of the week.
FirstWeekOfYear OPTIONAL It specifies the first week of the year.

Settings:

The firstdayofweek has the following settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting
vbSunday 1 Sunday
vbMonay 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

The setting of the firstweekofyear argument is below:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Consider starting week which contains January 1.
vbFirstFourDays 2 The week that contains at least four days in the year considers as starting week.
vbFirstFullWeek 3 Start with the first full week of the year.


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:

Symbol Range
d 1-31 (Day of the month without leading zero)
dd 01-31 (Day of the month with leading zero)
w 1-7 (Day of Week starts with Sunday as 1)
ww 1-53 (Week of the year without leading zero. January 1 considered as week 1)
m 1-12 (Month of the year without leading zero, like January=1)
mm 01-12 (Month of the year with leading zero, like January=01)
mmm Presents abbreviated month names.
mmmm Presents full month name.
y 1-366 (Days of the year)
yy 00-99 ( Last two digits of the year)
yyyy 100-9999 (Three or four digits of the year)

Time Symbol:

Symbol Range
h 0-23 (Hour of the day without leading zero. 1-12 restricted when AM or PM are mentioned)
hh 00-23 (Hour of the day with leading zero. 01-12 restricted when AM or PM are mentioned)
n 0-59 (Minutes of the hour without leading zero)
nn 00-59 (Minutes of the hour with leading zero)
m 0-59 (Minutes of the hour without leading zero). Only when h or hh ar mentioned.
mm 00-59 (Minutes of the hour with leading zero). Only when h or hh ar mentioned.
s 0-59 (Seconds of the minute without leading zero)
ss 00-59 (Seconds of the minute with leading zero)

4 Examples of Using Now and Format Functions in Excel VBA

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

1. Use of Now Function in 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.

Use of Now Function in VBA

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

Use of Now Function in VBA

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. Use of 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

Use of Format Function in VBA

Step 3:

  • Then press F5 and run the code.

Use of Format Function in VBA

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

Read more: How to Format Date with VBA in Excel


Similar Readings


3. Combination of 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 get from the Now function.

Step 1:

  • Create a macro for this example named Test3.

Step 2:

  • Now, put the below code o 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

Combination of Now and Format Function in VBA

Step 3:

  • Run the code by pressing the F5 button.

Combination of Now and Format Function in VBA

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


4. Different Presentations of Now Function Using the 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

Different Presentations of Now Function Using the Format Function

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

Different Presentations of Now Function Using the Format Function

Step 5:

  • After pressing F5 we get the below result.

Different Presentations of Now Function Using the Format Function

We have more 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.


Conclusion

In this article, we showed some examples of the Now and Format function in Excel VBA. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo