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.
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.
exampleDate = DateValue("Jun 19, 2010")
Note: Use Month and Day to get the month and day of a date.
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.
firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)
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.
To get the current date and time, use the Now function.
Note: replace a date, such as “June 19, 2010” with Now and you can use all the functions described above on the current date!
The following macro gets the hour of the current time.
Note: Use Minute and Second to get the minute and second of the current time.
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.
Now, to clearly see that Excel handles times internally as numbers between 0 and 1, add the following code lines:
y = TimeValue("09:20:01")
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.