Date and Time

Dates and times in Excel VBA can be manipulated in many ways. This chapter teaches you how to get the year, month and day of an Excel VBA date, how to add a number of days to a date, how to get the current date and time, how to get the hour, minute and second of the current time and how to convert a string to a time serial number.
Place a command button on your worksheet and add the code lines described in this chapter. To execute the code lines, click the command button on the sheet.

Year, Month, Day of a Date

The following macro gets the year of a date. First, we declare a date using the Dim statement. To initialize a date, we use the DateValue function.
Code:

Dim exampleDate As Date

exampleDate = DateValue("Jun 19, 2010")

MsgBox Year(exampleDate)

Result:

Note: Use Month and Day to get the month and day of a date.

DateAdd

To add a number of days to a date, use the DateAdd function. The DateAdd function has three arguments. Fill in "d" for the first argument since we want to add days. Fill in 3 for the second argument to add 3 days. The third argument represents the date, to which in this example, the number of days will be added.
Code:

Dim firstDate As Date, secondDate As Date

firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)

MsgBox secondDate

Result:

Note: Change "d" to "m" to add a number of months to a date. Place your cursor on DateAdd in Excel VBA and click on F1 for help on the other interval specifiers. The format of the date depends on your windows regional settings.

Current Date & Time

To get the current date and time, use the Now function.
Code:

MsgBox Now

Result:

Note: replace a date, such as “June 19, 2010” with Now and you can use all the functions described above on the current date!

Hour, Minute and Second

The following macro gets the hour of the current time.
Code:

MsgBox Hour(Now)

Result:

Note: Use Minute and Second to get the minute and second of the current time.

TimeValue

The TimeValue function converts a string to a time serial number. The time's serial number is a number between 0 and 1. For example, noon (halfway through the day) is represented as 0.5.
Code:

MsgBox TimeValue("9:20:01 am")

Result:

Now, to clearly see that Excel handles times internally as numbers between 0 and 1, add the following code lines:

Dim y As Double
y = TimeValue("09:20:01")
MsgBox y

Result:

We hope you found this information about date and time functions in Excel VBA useful.