Date and Time in MS Excel VBA

In this tutorial, you will learn how to use Data and Time functions in MS Excel VBA.

To have a better understanding and implementation of these functions, you will need to place a Command Button on MS Excel Worksheet and following codes under the Click event of Command Button.

DateValue()

DateValue function is used to set an initial value to a date variable. This function converts a string formatted date into a Date type.

Try the following code for better understanding:

MsgBox DateValue(“12-June-16”)

Result:

Year, Month, Day of a Date

To extract the Year, Month or a Day part of a specific date, you can use Year(), Month() or Day() function.

Try the following code for better understanding:

Dim dtpDate As Date

dtpDate = DateValue(“12-June-16”)

MsgBox Year(dtpDate) & vbCrLf & Month(dtpDate) & vbCrLf & Day(dtpDate)

Result:

In above implementation, you will observe the implementation of string joining where a new-line code (vbCrLf) is used to give a line break between the Year, Month and Date values.

DateAdd

DateAdd function is used to add number of days, month or year in a specified date. This function requires 3 arguments – 1^st argument is to select which part of the date needs to be added (or subtracted).

For date, use d
For month, use m
For year, use y

The second arguments refers to the number of days, month or year and finally the third argument refers to the specified date.

Try the following code for better understanding:

Dim dtpDate As Date

dtpDate = DateValue(“12-June-16”)

MsgBox DateAdd(“d”, 3, dtpDate)

Result:

In the above example, 3 days have been added to the specified date. You can increase the month or year by simply replacing the “d” with “m” or “y”.

Current Date and Time

Now() function is used to get the current system date and time

Try the following code for better understanding:

MsgBox Now

Result:

Hour, Minute, Second

To extract the Hour, Minute or a Second part of a specified time, you can use Hour(), Minute() or Second() function.

Try the following code for better understanding:

MsgBox Hour(Now) & vbCrLf & Minute(Now) & vbCrLf & Second(Now)

Result:

In above implementation, you will observe the implementation of string joining where a new-line code (vbCrLf) is used to give a line break between the Hour, Minute and Second values.