Home » Tutorials » VBA Tutorials » VBA – Date-Time Function

VBA – Date-Time Function

by
VBA - Date-Time Function Shout4Education

VBScript Date and Time Functions help the developers to convert date and time from one format to another or to express the date or time value in the format that suits a specific condition.

Date Functions

Sr.No. Function & Description
1 Date

A Function, which returns the current system date.
2 CDate

A Function, which converts a given input to date.
3 DateAdd

A Function, which returns a date to which a specified time interval has been added.
4 DateDiff

A Function, which returns the difference between two time period.
5 DatePart

A Function, which returns a specified part of the given input date value.
6 DateSerial

A Function, which returns a valid date for the given year, month, and date.
7 FormatDateTime

A Function, which formats the date based on the supplied parameters.
8 IsDate

A Function, which returns a Boolean Value whether or not the supplied parameter is a date.
9 Day

A Function, which returns an integer between 1 and 31 that represents the day of the specified date.
10 Month

A Function, which returns an integer between 1 and 12 that represents the month of the specified date.
11 Year

A Function, which returns an integer that represents the year of the specified date.
12 MonthName

A Function, which returns the name of the particular month for the specified date.
13 WeekDay

A Function, which returns an integer(1 to 7) that represents the day of the week for the specified day.
14 WeekDayName

A Function, which returns the weekday name for the specified day.

VBA – Date Function

The Function returns the current system date.

Syntax

date()

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a as Variant
   a = date()
   msgbox "The Value of a : " & a
End Sub
When you execute the function, it produces the following output.
The Value of a : 08/04/2020

 

VBA – CDate Function

The Function converts a valid date and time expression to type date.

Syntax

cdate(date)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a as Variant
   Dim b as Variant
a = cdate("Jan 01 2020")
   msgbox("The Value of a : " & a)
b = cdate("31 Dec 2050")
   msgbox("The Value of b : " & b)
End Sub
When you execute the function, it produces the following output.
The Value of a : 1/01/2020
The Value of b : 31/12/2050

VBA – DateAdd Function

A Function, which returns a date to which a specified time interval has been added.

Syntax

DateAdd(interval,number,date)

Parameter Description

  • Interval − A required parameter. It can take the following values.
    • d – day of the year
    • m – month of the year
    • y – year of the year
    • yyyy – year
    • w – weekday
    • ww – week
    • q – quarter
    • h – hour
    • m – minute
    • s – second
  • Number − A required parameter. It can take both positive and negative parameters.
  • Date − A required parameter. A variant or literal representing the date to which an interval is added.

Example

Private Sub Constant_demo_Click()
   ' Positive Interal
   date1 = 27-Jun-1894
   msgbox("Line 1 : " &DateAdd("yyyy",1,date1))
   msgbox("Line 2 : " &DateAdd("q",1,date1))
   msgbox("Line 3 : " &DateAdd("m",1,date1))
   msgbox("Line 4 : " &DateAdd("y",1,date1))
   msgbox("Line 5 : " &DateAdd("d",1,date1))
   msgbox("Line 6 : " &DateAdd("w",1,date1))
   msgbox("Line 7 : " &DateAdd("ww",1,date1))
   msgbox("Line 8 : " &DateAdd("h",1,"01-Jan-2013 12:00:00"))
   msgbox("Line 9 : " &DateAdd("n",1,"01-Jan-2013 12:00:00"))
   msgbox("Line 10 : "&DateAdd("s",1,"01-Jan-2013 12:00:00"))
Negative Interval
   msgbox("Line 11 : " &DateAdd("yyyy",-1,date1))
   msgbox("Line 12 : " &DateAdd("q",-1,date1))
   msgbox("Line 13 : " &DateAdd("m",-1,date1))
   msgbox("Line 14 : " &DateAdd("y",-1,date1))
   msgbox("Line 15 : " &DateAdd("d",-1,date1))
   msgbox("Line 16 : " &DateAdd("w",-1,date1))
   msgbox("Line 17 : " &DateAdd("ww",-1,date1))
   msgbox("Line 18 : " &DateAdd("h",-1,"01-Jan-2013 12:00:00"))
   msgbox("Line 19 : " &DateAdd("n",-1,"01-Jan-2013 12:00:00"))
   msgbox("Line 20 : " &DateAdd("s",-1,"01-Jan-2013 12:00:00")) 
End Sub
When you execute the above function, it produces the following output.
Line 1 : 27/06/1895
Line 2 : 27/09/1894
Line 3 : 27/07/1894
Line 4 : 28/06/1894
Line 5 : 28/06/1894
Line 6 : 28/06/1894
Line 7 : 4/07/1894
Line 8 : 1/01/2013 1:00:00 PM
Line 9 : 1/01/2013 12:01:00 PM
Line 10 : 1/01/2013 12:00:01 PM
Line 11 : 27/06/1893
Line 12 : 27/03/1894
Line 13 : 27/05/1894
Line 14 : 26/06/1894
Line 15 : 26/06/1894
Line 16 : 26/06/1894
Line 17 : 20/06/1894
Line 18 : 1/01/2013 11:00:00 AM
Line 19 : 1/01/2013 11:59:00 AM
Line 20 : 1/01/2013 11:59:59 AM

VBA – DateDiff Function

A Function, which returns the difference between two specified time intervals.

Syntax

DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])

Parameter Description

  • Interval − A required parameter. It can take the following values.
    • d – day of the year
    • m – month of the year
    • y – year of the year
    • yyyy – year
    • w – weekday
    • ww – week
    • q – quarter
    • h – hour
    • m – minute
    • s – second
  • Date1 and Date2 − Required parameters.
  • Firstdayofweek − An optional parameter. Specifies the first day of the week. It can take the following values.
    • 0 = vbUseSystemDayOfWeek – Use National Language Support (NLS) API setting
    • 1 = vbSunday – Sunday
    • 2 = vbMonday – Monday
    • 3 = vbTuesday – Tuesday
    • 4 = vbWednesday – Wednesday
    • 5 = vbThursday – Thursday
    • 6 = vbFriday – Friday
    • 7 = vbSaturday – Saturday
  • Firstdayofyear − An optional parameter. Specifies the first day of the year. It can take the following values.
    • 0 = vbUseSystem – Use National Language Support (NLS) API setting
    • 1 = vbFirstJan1 – Start with the week in which January 1 occurs (default)
    • 2 = vbFirstFourDays – Start with the week that has at least four days in the new year
    • 3 = vbFirstFullWeek – Start with the first full week of the new year

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim fromDate as Variant
   fromDate = "01-Jan-09 00:00:00"
Dim toDate as Variant
   toDate = "01-Jan-10 23:59:00"
msgbox("Line 1 : " &DateDiff("yyyy",fromDate,toDate))
   msgbox("Line 2 : " &DateDiff("q",fromDate,toDate))
   msgbox("Line 3 : " &DateDiff("m",fromDate,toDate))
   msgbox("Line 4 : " &DateDiff("y",fromDate,toDate))
   msgbox("Line 5 : " &DateDiff("d",fromDate,toDate))
   msgbox("Line 6 : " &DateDiff("w",fromDate,toDate))
   msgbox("Line 7 : " &DateDiff("ww",fromDate,toDate))
   msgbox("Line 8 : " &DateDiff("h",fromDate,toDate))
   msgbox("Line 9 : " &DateDiff("n",fromDate,toDate))
   msgbox("Line 10 : "&DateDiff("s",fromDate,toDate))
End Sub
When you execute the above function, it produces the following output.
Line 1 : 1
Line 2 : 4
Line 3 : 12
Line 4 : 365
Line 5 : 365
Line 6 : 52
Line 7 : 52
Line 8 : 8783
Line 9 : 527039
Line 10 : 31622340

VBA – DatePart Function

A Function, which returns the specific part of the given date.

Syntax

DatePart(interval,date[,firstdayofweek[,firstweekofyear]])

Parameter Description

  • Interval − A required parameter. It can take the following values.
    • d – day of the year.
    • m – month of the year
    • y – year of the year
    • yyyy – year
    • w – weekday
    • ww – week
    • q – quarter
    • h – hour
    • n – minute
    • s – second
  • Date1 − A required parameter.
  • Firstdayofweek − An optional parameter. Specifies the first day of the week. It can take the following values.
    • 0 = vbUseSystemDayOfWeek – Use National Language Support (NLS) API setting
    • 1 = vbSunday – Sunday
    • 2 = vbMonday – Monday
    • 3 = vbTuesday – Tuesday
    • 4 = vbWednesday – Wednesday
    • 5 = vbThursday – Thursday
    • 6 = vbFriday – Friday
    • 7 = vbSaturday – Saturday
  • Firstdayofyear − An optional parameter. Specifies the first day of the year. It can take the following values.
    • 0 = vbUseSystem – Use National Language Support (NLS) API setting
    • 1 = vbFirstJan1 – Start with the week in which January 1 occurs (default)
    • 2 = vbFirstFourDays – Start with the week that has at least four days in the new year
    • 3 = vbFirstFullWeek – Start with the first full week of the new year

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim Quarter as Variant
   Dim DayOfYear as Variant
   Dim WeekOfYear as Variant
Date1 = "2013-01-15"
   Quarter    = DatePart("q", Date1)
msgbox ("Line 1 : " & Quarter)
   DayOfYear  = DatePart("y", Date1)
msgbox ("Line 2 : " & DayOfYear)
   WeekOfYear = DatePart("ww", Date1)
msgbox ("Line 3 : " & WeekOfYear)
   msgbox("Line 4 : " & DatePart("m",Date1))  
End Sub
When you execute the above function, it produces the following output.
Line 1 : 1
Line 2 : 15
Line 3 : 3
Line 4 : 1

VBA – DateSerial Function

A Function, which returns a date for the specified day, month, and year parameters.

Syntax

DateSerial(year,month,day)

Parameter Description

  • Year − A required parameter. A number between 100 and 9999 or a numeric expression. Values between 0 and 99 are interpreted as the years 1900 to 1999. For all other year arguments, use a complete four-digit year.
  • Month − A required parameter. It can also be in the form of an expression, which should range from 1 to 12.
  • Day − A required parameter. It can also be in the form of an expression, which should range from 1 to 31.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox(DateSerial(2013,5,10))
End Sub
When you execute the above function, it produces the following output.
10/05/2014

VBA – Format DateTime Function

A Function, which helps the developers to format and return a valid date and time expression.

Syntax

FormatDateTime(date,format)

Parameter Description

  • Date − A required parameter.
  • Format − An optional parameter. The Value that specifies the date or time format to be used. It can take the following values.
    • 0 = vbGeneralDate – Default
    • 1 = vbLongDate – Returns date
    • 2 = vbShortDate – Returns date
    • 3 = vbLongTime – Returns time
    • 4 = vbShortTime – Returns time

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   d = ("2013-08-15 20:25")
   msgbox("Line 1 : " & FormatDateTime(d))
   msgbox("Line 2 : " & FormatDateTime(d,1))
   msgbox("Line 3 : " & FormatDateTime(d,2))
   msgbox("Line 4 : " & FormatDateTime(d,3))
   msgbox("Line 5 : " & FormatDateTime(d,4))
End Sub
When you execute the above function, it produces the following output.
Line 1 : 15/08/2013 8:25:00 PM 
Line 2 : Thursday, 15 August 2013
Line 3 : 15/08/2013
Line 4 : 8:25:00 PM
Line 5 : 20:25

VBA – IsDate Function

A Function, which returns a Boolean value whether or not the given input is a date.

Syntax

IsDate(expression)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1 : " & IsDate("Nov 03, 1950"))
   msgbox("Line 2 : " & IsDate(#01/31/20#))
   msgbox("Line 3 : " & IsDate(#05/31/20 10:30 PM#))
End Sub
When you execute the above function, it produces the following output.
Line 1 : True
Line 2 : True
Line 3 : True

VBA – Day Function

The Day function returns a number between 1 and 31 that represents the day of the specified date.

Syntax

Day(date)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox(Day("2013-06-30"))
End Sub
When you execute the above function, it produces the following output.
30

VBA – Month Function

The Month function returns a number between 1 and 12 that represents the month of the specified date.

Syntax

Month(date)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox(Month("2013-06-30"))
End Sub
When you execute the above function, it produces the following output.
6

VBA – Year Function

The Year function returns an integer that represents a year of the specified date.

Syntax

Year(date)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox(Year("2013-06-30"))
End sub
When you execute the above function, it produces the following output.
2013

VBA – Month Name

The MonthName function returns the name of the month for the specified date.

Syntax

MonthName(month[,toabbreviate])

Parameter Description

  • Month − A required parameter. It specifies the number of the month.
  • Toabbreviate − An optional parameter. A Boolean value that indicates if the month name is to be abbreviated. If left blank, the default value would be taken as False.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1 : " & MonthName(01,True))
   msgbox("Line 2 : " & MonthName(01,false))
   msgbox("Line 3 : " & MonthName(07,True))
   msgbox("Line 4 : " & MonthName(07,false))
End Sub
When you execute the above function, it produces the following output.
Line 1 : Jan
Line 2 : January
Line 3 : Jul
Line 4 : July

VBA – WeekDay

The WeekDay function returns an integer from 1 to 7 that represents the day of the week for the specified date.

Syntax

Weekday(date[,firstdayofweek])

Parameter Description

  • Date − A required parameter. The weekday will return a specified date.
  • Firstdayofweek − An optional parameter. Specifies the first day of the week. It can take the following values.
    • 0 = vbUseSystemDayOfWeek – Use National Language Support (NLS) API setting
    • 1 = vbSunday – Sunday
    • 2 = vbMonday – Monday
    • 3 = vbTuesday – Tuesday
    • 4 = vbWednesday – Wednesday
    • 5 = vbThursday – Thursday
    • 6 = vbFriday – Friday
    • 7 = vbSaturday – Saturday

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1: " & Weekday("2013-05-16",1))
   msgbox("Line 2: " & Weekday("2013-05-16",2))
   msgbox("Line 3: " & Weekday("2013-05-16",2))
   msgbox("Line 4: " & Weekday("2010-02-16"))
   msgbox("Line 5: " & Weekday("2010-02-17"))
   msgbox("Line 6: " & Weekday("2010-02-18"))
End Sub
When you execute the above function, it produces the following output.
Line 1: 5
Line 2: 4
Line 3: 4
Line 4: 3
Line 5: 4
Line 6: 5

VBA – WeekDay Name

The WeekDayName function returns the name of the weekday for the specified day.

Syntax

WeekdayName(weekday[,abbreviate[,firstdayofweek]])

Parameter Description

  • Weekday − A required parameter. The number of the weekday.
  • Toabbreviate − An optional parameter. A Boolean value that indicates if the month name is to be abbreviated. If left blank, the default value would be taken as False.
  • Firstdayofweek − An optional parameter. Specifies the first day of the week.
    • 0 = vbUseSystemDayOfWeek – Use National Language Support (NLS) API setting
    • 1 = vbSunday – Sunday
    • 2 = vbMonday – Monday
    • 3 = vbTuesday – Tuesday
    • 4 = vbWednesday – Wednesday
    • 5 = vbThursday – Thursday
    • 6 = vbFriday – Friday
    • 7 = vbSaturday – Saturday

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1 : " &WeekdayName(3))
   msgbox("Line 2 : " &WeekdayName(2,True))
   msgbox("Line 3 : " &WeekdayName(1,False))
   msgbox("Line 4 : " &WeekdayName(2,True,))
   msgbox("Line 5 : " &WeekdayName(1,False,1))
End Sub
When you execute the above function, it produces the following output.
Line 1 : Tuesday
Line 2 : Mon
Line 3 : Sunday
Line 4 : Tue
Line 5 : Sunday

Time Functions

Sr.No. Function & Description
1 Now

A Function, which returns the current system date and time.
2 Hour

A Function, which returns an integer between 0 and 23 that represents the hour part of the given time.
3 Minute

A Function, which returns an integer between 0 and 59 that represents the minutes part of the given time.
4 Second

A Function, which returns an integer between 0 and 59 that represents the seconds part of the given time.
5 Time

A Function, which returns the current system time.
6 Timer

A Function, which returns the number of seconds and milliseconds since 12:00 AM.
7 TimeSerial

A Function, which returns the time for the specific input of hour, minute and second.
8 TimeValue

A Function, which converts the input string to a time format.

VBA – Now Function

The Function Now returns the current system date and time.

Syntax

Now()

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a as Variant
   a = Now()
   msgbox("The Value of a : " & a)
End Sub
When you execute the above function, it produces the following output.
The Value of a : 19/07/2013 3:04:09 PM

VBA – Hour Function

The Hour Function returns a number between 0 and 23 that represents the hour of the day for the specified time stamp.

Syntax

Hour(time)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1: " & Hour("3:13:45 PM"))
   msgbox("Line 2: " & Hour("23:13:45"))
   msgbox("Line 3: " & Hour("2:20 PM"))
End Sub
When you execute the above function, it produces the following output.
Line 1: 15
Line 2: 23
Line 3: 14

VBA – Minute Function

The Minute Function returns a number between 0 and 59 that represents the minute of the hour for the specified time stamp.

Syntax

Minute(time)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1: " & Minute("3:13:45 PM"))
   msgbox("Line 2: " & Minute("23:43:45"))
   msgbox("Line 3: " & Minute("2:20 PM"))
End Sub
When you execute the above function, it produces the following output.
Line 1: 13
Line 2: 43
Line 3: 20

VBA – Second Function

The Second Function returns a number between 0 and 59 that represents the second of the hour for the specified time stamp.

Syntax

Second(time)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1: " & Second("3:13:25 PM"))
   msgbox("Line 2: " & Second("23:13:45"))
   msgbox("Line 3: " & Second("2:20 PM"))
End Sub
When you execute the above function, it produces the following output.
Line 1: 25
Line 2: 45
Line 3: 0

VBA – Time Function

The Time Function returns the current system time.

Syntax

Time()

Example

Private Sub Constant_demo_Click()
   msgbox("Line 1: " & Time())
End Sub
When you execute the above function, it produces the following output.
Line 1: 3:29:15 PM

VBA – Timer Function

The Timer Function returns the number of seconds and milliseconds since 12:00 AM.

Syntax

Timer()

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Time is : " & Now())
   msgbox("Timer is: " & Timer())
End Sub
When you execute the above function, it produces the following output.
Time is : 19/07/2013 3:45:53 PM
Timer is: 56753.4

VBA – Time Serial Function

The TimeSerial function returns the time for the specified hour, minute, and second values.

Syntax

TimeSerial(hour,minute,second)

Parameter Description

  • Hour − A required parameter, which is an integer between 0 and 23 or any numeric expression.
  • Minute − A required parameter, which is an integer between 0 and 59 or any numeric expression.
  • Second − A required parameter, which is an integer between 0 and 59 or any numeric expression.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox(TimeSerial(20,1,2))
   msgbox(TimeSerial(,59,59))
   msgbox(TimeSerial(7*2,60/3,15+3))
End Sub
When you execute the above function, it produces the following output.
8:01:02 PM
12:59:59 AM
2:20:18 PM

VBA – Time Value Function

The TimeValue Function converts the given input string to a valid time.

Syntax

TimeValue(StringTime)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click() 
   msgbox(TimeValue("20:30"))
   msgbox(TimeValue("5:15"))
   msgbox(TimeValue("2:30:58"))
End Sub
When you execute the above function, it produces the following output.
8:30:00 PM
5:15:00 AM
2:30:58 AM

Related Posts

Leave a Comment