Lesson 11: Date and Time Functions

กก[Lesson 10] [Home] [Lesson 12]


Excel VBA provides various built-in date and time functions that allows us to write VBA codes involving dates and times. We can use date and time functions to display system date and time , add and substract data and time, converting string to date and more. 

11.1 Now, Date, Day, Weekday, WeekdayName, Month, MonthName and Year Functions.

The date and time functions are explained in Table 11.1.

Table 11.1 Date and Time Functions

FunctionDescription
Nowreturns current system date and time
Datereturns 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 abbrieviated 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 abbrieviated 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, Hour, Minute, Second and Timer Functions

The time functions are explained in Table 11.2.

Table 11.2 Time Functions

FunctionDescription
TimeReturns the current system time
HourReturns the hour from its argument
MinuteReturns the minute from its argument
SecondReturns the second from its argument
TimerReturns 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 substract 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 substract. The interval can be YYYY, m, w, ww, d, h, n, s, same as parameters for DatePart. The function with calculate 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

Figure 11.4


  Bookmark and Share



กก[Lesson 10] [Home] [Lesson 12]

Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved 

Contact: admin@excelvbatutor.com [Privacy Policy]