Date and Time

 

Year, Month, Day of a Date | DateAdd | Current Date & Time | Hour, Min, Sec | TimeValue

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:

The Result is the Year of the Excel VBA Date

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:

The Result is the Date three days later

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:

The Result is the Current Date and Time

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:

The Result is the Hour of the Current Time

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:

TimeValue Function 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:

Time Serial Number

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

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: Date and Time    |   Go to Next Topic: Function and Sub

Learn more about dates and time, Login to the right >>
100 easy to follow Excel VBA examples. Limited time-offer: $39.00 but only $29.00. Ends on 29th February.