Excel VBA Lesson 11: Mastering Date and Time Functions in Excel VBA

Home > Excel VBA Tutorial > Date and Time Functions

Key Learning Points: In this lesson, you'll learn how to work with dates and times in Excel VBA, including retrieving system dates/times, extracting components, and performing calculations.

Excel VBA provides powerful built-in date and time functions that allow you to:

Display and manipulate system dates and times
Perform date arithmetic (add/subtract dates)
Convert between different date formats
Extract specific components (day, month, year, etc.)
Create time-based automation in your macros

11.1 Essential Date Functions

These functions help you work with calendar dates in your VBA applications:

Table 11.1: VBA Date Functions and Their Uses
Function	Description	Example Usage
`Now`	Returns current system date and time	`Cells(1,1) = Now`
`Date`	Returns current system date	`Cells(1,1) = Date`
`Day(Date)`	Returns day of month (1-31)	`Day("12/25/2023") → 25`
`Weekday(Date)`	Returns weekday as number (1=Sunday)	`Weekday(Date)`
`WeekdayName()`	Returns weekday name (full or abbreviated)	`WeekdayName(Weekday(Date))`
`Month(Date)`	Returns month number (1-12)	`Month("12/25/2023") → 12`
`MonthName()`	Returns month name (full or abbreviated)	`MonthName(Month(Date))`
`Year(Date)`	Returns the year	`Year(Date)`

Practical Example 11.1: Displaying Date Components

This example demonstrates how to extract and display various date components in Excel:

Private Sub CommandButton1_Click()
  ' Display various date components in column B
  Cells(1, 2) = Now                  ' Current date and time
  Cells(2, 2) = Date                 ' Current date
  Cells(3, 2) = Day(Date)            ' Day of month
  Cells(4, 2) = Weekday(Date)        ' Weekday number
  Cells(5, 2) = WeekdayName(Weekday(Date))       ' Full weekday name
  Cells(6, 2) = WeekdayName(Weekday(Date), True) ' Abbreviated weekday
  Cells(7, 2) = Month(Date)          ' Month number
  Cells(8, 2) = MonthName(Month(Date))           ' Full month name
  Cells(9, 2) = MonthName(Month(Date), True)     ' Abbreviated month
  Cells(10, 2) = Year(Date)          ' Current year
End Sub

Pro Tip: Use Format() function to display dates in custom formats like "dd-mmm-yyyy" or "mmmm d, yyyy".

Excel output showing date function results — **Figure 11.1:** Output from Example 11.1 showing various date components

11.2 Working with Time Functions

Excel VBA also provides functions specifically for working with times:

Table 11.2: VBA Time Functions
Function	Description	Example Usage
`Time`	Current system time	`Cells(1,1) = Time`
`Hour(time)`	Hour component (0-23)	`Hour("3:45 PM") → 15`
`Minute(time)`	Minute component (0-59)	`Minute(Now)`
`Second(time)`	Second component (0-59)	`Second(Time)`
`Timer`	Seconds since midnight	`Timer`

Practical Example 11.2: Displaying Time Components

This example shows how to work with time values in VBA:

Private Sub CommandButton1_Click()
  ' Display various time components in column B
  Cells(1, 2) = Time             ' Current time
  Cells(2, 2) = Hour(Time)       ' Current hour
  Cells(3, 2) = Minute(Time)     ' Current minute
  Cells(4, 2) = Second(Time)     ' Current second
  Cells(5, 2) = Timer            ' Seconds since midnight
  
  ' Bonus: Calculate time difference
  Dim startTime As Double
  startTime = Timer
  ' ... some code that takes time to execute ...
  Cells(6, 2) = Timer - startTime & " seconds elapsed"
End Sub

Excel output showing time function results — **Figure 11.2:** Output from Example 11.2 showing time components

Practical Applications

These date and time functions are commonly used for:

Timestamping data entries
Creating automated reports with date headers
Calculating durations and time differences
Triggering time-based macros
Age calculations and date validations

Summary

✅ In This Lesson, You Learned:

Now and Date functions retrieve current date/time
Use Day, Month, Year to extract date components
WeekdayName and MonthName provide formatted output
Time function gets current system time
Hour, Minute, Second break down time values
Timer is useful for measuring code execution time
Combine these functions for powerful date/time manipulations

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Share this lesson:

Contact: Facebook Page | Email