Excel VBA – Date and Time in Excel

Excel VBA – Date and Time in Excel: Excel VBA is a powerful programming language that can be used to automate tasks in Excel. One of the most common tasks that VBA can be used for is working with dates and times. There are a number of VBA functions that can be used to manipulate dates and times, and this article will provide an overview of some of the most commonly used functions.

VBA Date and Time Functions

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet. The following are some of the most commonly used VBA date and time functions:

DATE

The DATE function returns a date value based on the year, month, and day arguments. For example, the following code would return the date May 17, 2023:

Code snippet

Private Sub CommandButton1_Click()
Dim exampleDate As Date

exampleDate = DateValue("May 17, 2023")

MsgBox DateValue(exampleDate)
End Sub

Result:

NOW

The NOW function returns the current date and time. For example, the following code would return the current date and time:

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateValue("May 17, 2023")

MsgBox Now
End Sub

Result:

DAY

The DAY function returns the day of the month for a given date. For example, the following code would return the day of the month for the date May 17, 2023:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateValue("May 17, 2023")

MsgBox Day(exampleDate)
End Sub

Result:

MONTH

The MONTH function returns the month of the year for a given date. For example, the following code would return the month of the year for the date May 17, 2023:

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateValue("May 17, 2023")

MsgBox Month(exampleDate)
End Sub

YEAR

The YEAR function returns the year for a given date. For example, the following code would return the year for the date May 17, 2023:

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateValue("May 17, 2023")

MsgBox Year(exampleDate)
End Sub

HOUR

The HOUR function returns the hour of the day for a given time. For example, the following code would return the hour of the day for the time of Now:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = Now

MsgBox Hour(exampleDate)

End Sub

Result:

MINUTE

The MINUTE function returns the minute of the hour for a given time. For example, the following code would return the minute of the hour for the time of Now:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = Now

MsgBox Minute(exampleDate)

End Sub

Result:

SECOND

The SECOND function returns the second of the minute for a given time. For example, the following code would return the second of the minute for the time of Now:

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = Now

MsgBox Second(exampleDate)

End Sub

Result:

TIME

The TIME function returns a time value based on the hour, minute, and second arguments. For example, the following code would return the time of Now:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = Now

MsgBox Format(exampleDate, "hh:mm:ss")

End Sub

Result:

DATEADD

The DATEADD function adds a specified number of days, months, years, hours, minutes, seconds, or weeks to a given date. For example, the following code would add 1 day to the date May 17, 2023:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateAdd("d", 1, "2023-05-17")

MsgBox DateValue(exampleDate)

End Sub

Result:

DATEDIFF

The DATEDIFF function returns the number of days, months, years, hours, minutes, seconds, or weeks between two dates. For example, the following code would return the number of days between the dates May 1, 2023 and February 1, 2023:

Code snippet

Private Sub CommandButton1_Click()

Dim exampleDate As Date

exampleDate = DateDiff("d", "2023-05-01", "2023-06-01")

MsgBox Day(exampleDate)

End Sub

Result:

Working with Date and Time in Excel VBA

The VBA date and time functions can be used to perform a variety of tasks, such as:

• Formatting dates and times
• Converting dates and times
• Calculating dates and times
• Comparing dates and times
• Sorting dates and times
• Filtering dates and times
• Working with date ranges

Conclusion

The VBA date and time functions are a powerful tool that can be used to automate tasks in Excel. By understanding how to use these functions, you can save time and improve the efficiency of your work.