<<Lesson 10>> [Contents] <<Lesson 12>>
Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more.
11.1 Date Functions
The date functions are explained in Table 11.1.
Table 11.1 Date and Time Functions
| Function | Description |
|---|---|
| Now | returns current system date and time |
| Date | returns current system date |
| Day(Date) | Returns the day of the month for the date specified in the argument |
| Weekday(Date) | Returns weekday as an integer for the date specified in the argument |
| WeekdayName(Weekday(Date)) | Returns the name of weekday for the date specified in the argument |
| WeekdayName(Weekday(Date), True) | Returns the abbreviated name of weekday for the date specified in the argument |
| Month(Date) | Returns the month of the year in integer for the date specified in the argument |
| MonthName(Month(Date)) | Returns the name of month of the year for the date specified in the argument |
| MonthName(Month(Date)) | Returns the abbreviated name of month of the year for the date specified in the argument |
| Year(Date) | Returns the year in integer for the date specified in the argument |
Example 11.1
Private Sub CommandButton1_Click()
Cells(1, 2) = Now
Cells(2, 2) = Date
Cells(3, 2) = Day(Date)
Cells(4, 2) = Weekday(Date)
Cells(5, 2) = WeekdayName(Weekday(Date))
Cells(6, 2) = WeekdayName(Weekday(Date), “true”)
Cells(7, 2) = Month(Date)
Cells(8, 2) = MonthName(Month(Date))
Cells(9, 2) = MonthName(Month(Date), “true”)
Cells(10, 2) = Year(Date)
End Sub
The output is as shown in Figure 11.1
Figure 11.1
11.2 Time Functions
The time functions are explained in Table 11.2.
Table 11.2 Time Functions
| Function | Description |
|---|---|
| Time | Returns the current system time |
| Hour | Returns the hour from its argument |
| Minute | Returns the minute from its argument |
| Second | Returns the second from its argument |
| Timer | Returns the number of seconds since midnight |
Example 11.2
Private Sub CommandButton1_Click()
Cells(1, 2) = Time
Cells(2, 2) = Hour(Time)
Cells(3, 2) = Minute(Time)
Cells(4, 2) = Second(Time)
Cells(5, 2) = Timer
End Sub
The output is shown in Figure 11.2
Figure 11.2
11.3 DatePart Function
The DatePart function returns the part of the date specified in the arguments. The arguments are:
YYYY- Year
q- Quarter
m- Month
Y- Day of Year
d- Day
w- Weekday
ww- Week
h- Hour
n- Minute
s- Second
Example 11.3
Private Sub CommandButton1_Click()
Cells(1, 2) = DatePart(“YYYY”, Now)
Cells(2, 2) = DatePart(“q”, Now)
Cells(3, 2) = DatePart(“m”, Now)
Cells(4, 2) = DatePart(“y”, Now)
Cells(5, 2) = DatePart(“d”, Now)
Cells(6, 2) = DatePart(“w”, Now)
Cells(7, 2) = DatePart(“ww”, Now)
Cells(8, 2) = DatePart(“h”, Now)
Cells(9, 2) = DatePart(“n”, Now)
Cells(10, 2) = DatePart(“s”, Now)
End Sub
The argument Now is to return the current date and time.The output is shown in Figure 11.3
Figure 11.3
11.4 DateAdd and DateDiff Functions
The function DateAdd is to add dates and the DateDiff is the function to subtract dates.
The syntax of DateAdd is
DateAdd(“t”,n,date)
Where t indicates the interval of the part of the date to add, either d(day), m(month) or year and n is the value to add.
The syntax of DateDiff is
DateDiff(“t”,date1,date2)
Where t indicates the interval of the part of the date to subtract. The interval can be YYYY, m, w, ww, d, h, n, s, same as parameters for DatePart. The function of calculating the difference between date1 and date2.
Example 11.4
Private Sub CommandButton1_Click()
Cells(1, 2) = Now
Cells(2, 2) = DateAdd(“yyyy”, 2, Now)
Cells(3, 2) = DateAdd(“m”, 10, Now)
Cells(4, 2) = DateAdd(“d”, 100, Now)
Cells(5, 2) = DateAdd(“h”, 10, Now)
Cells(6, 2) = DateAdd(“YYYY”, 3, “2015/3/28”)
Cells(7, 2) = DateDiff(“YYYY”, Now, “2020/4/16”)
Cells(8, 2) = DateDiff(“m”, Now, “2020/4/16”)
Cells(9, 2) = DateDiff(“ww”, Now, “2020/4/16”)
Cells(10, 2) = DateDiff(“d”, Now, “2020/4/16”)
Cells(11, 2) = DateDiff(“YYYY”, “2016/5/20”, “2020/4/16”)
Cells(12, 2) = DateDiff(“m”, “2016/5/20”, “2020/4/16”)
End Sub
The output is shown in Figure 11.4
