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.
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:
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: We will show some examples using the Now and Format function in Excel VBA. In this example, we will show the use of the Now function in VBA. Step 1: Step 2: Step 3: Step 4: Here, we applied the Now function and the date and time of the present time with date. In this example, we will show the use of the Format function in VBA. Step 1: Step 2: Step 3: 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 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: Step 2: Step 3: Finally, we are getting the present date and time according to our specified format. Here, we will present the Now function in different ways using the Format function. Step 1: Step 2: Step 3: Here we get the result in the long-time format. Now, we will represent in more ways. Step 4: Step 5: We have more other options to present the Now function. According to the user’s needs, they can modify them. 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. 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.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)
1. Using NOW Function in Excel VBA
Sub Test1()
Dim Msg
Msg = Now()
MsgBox Msg
End Sub
2. Using FORMAT Function in VBA
Sub Test2()
Dim Msg
Dim date1 As Date
date1 = #1/27/1993#
Msg = Format(date1, "Long Date")
MsgBox Msg
End Sub
3. Combining NOW and FORMAT Function in VBA
Sub Test3()
Dim Msg
Dim date1 As Date
date1 = Now()
Msg = Format(date1, "mmmm dd, yyyy hh:mm AM/PM")
MsgBox Msg
End Sub
4. Formatting Current Time in Different Ways using NOW and FORMAT Function
Sub Test4()
Range("C4") = Format(Now(), "Long Time")
End Sub
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
Things to Remember
Conclusion
Further Readings