In this article, I’ll show you various built-in VBA date and time functions that we can leverage in UFT automation scripts or in an excel macro and other places to cater different needs. Also, while doing automation sometimes we have to deal with different date formats and it becomes a troublesome activity to convert the date in the required formats at various places. So, at the end of this article, I will show you how to write our own custom user-defined VBA date function that can covert date format to other required date formats.
So let’s get started.
VBA Date Functions
The VBA Date Function returns the current system date in the format as it is shown in the taskbar of the Windows machine.
Syntax:
You can call this function in your code by simply writing Date or Date(). It has no arguments.
Example:
|
1 2 3 4 5 |
Dim myDate myDate = Date ' myDate contains the current system date. MsgBox myDate ' Will display 28-02-2022 as per the system date shown in the Taskbar |
VBA Time Function
The Time Function returns the current time.
Syntax:
You can call this function in your code by simply writing Time or Time(). It has no arguments.
Example:
|
1 2 3 4 5 |
Dim currTime currTime = Time ' currTime contains the current system time. MsgBox currTime ' Will display current time as 8:40:20 |
VBA Now Function
The VBA Now Function returns the current date along with time.
Syntax:
You can call this function in your code by simply writing Now or Now(). It has no arguments.
Example:
|
1 2 3 4 5 |
Dim currDateTime currDateTime = Now ' currDateTime contains the current system date and time. MsgBox currDateTime ' Will display current date and time |
VBA DateAdd Function
The VBA DateAdd Function adds a date or time interval to a date or time, and return the resulting date/time value.
Syntax:
DateAdd(interval, number, date)
Arguments:
interval
Required Value. A string expression that is the interval you want to add. See argument setting section for values.
number
Required Value. A numeric expression that is the number of intervals you want to add. The numeric expression can either be positive, for dates in the future, or negative, for dates in the past.
date
Required Value. Variant or literal representing the date to which interval is added.
Argument Settings:
The interval argument can have the following values:
| Interval Argument Value | Description |
|---|---|
| d | Day |
| m | Month |
| q | Quarter |
| y | Day of year |
| yyyy | Year |
| w | Weekday |
| ww | Week of year |
| h | Hour |
| n | Minute |
| s | Second |
Remarks: You can use the DateAdd function to add or subtract a specified time interval from a date.
Example 1: Add 10 days to the March 5.
|
1 2 3 4 5 |
Dim newDate newDate = DateAdd("d", 10, "5-Mar-22") MsgBox newDate ' Will display 15-03-2022 |
In the same fashion, you can also subtract days from a date if you provide a negative number in the second argument.
Example 2: Add 2 months of the March 5.
|
1 2 3 4 5 |
Dim newDate newDate = DateAdd("m", 10, "5-Mar-22") MsgBox newDate ' Will display 05-05-2022 |
Example 3: Subtract 5 days of the March 5.
|
1 2 3 4 5 |
Dim newDate newDate = DateAdd("d", -5, "5-Mar-22") MsgBox newDate ' Will display 28-02-2022 |
Example 4: Add 10 minutes to current time.
|
1 2 3 4 5 |
Dim newTime newTime = DateAdd("n", 10, Time) MsgBox newTime' Will add 10 minutes to cuurent system time and display new time like 11:59:01 |
VBA DateDiff Function
The DateDiff Function returns number of intervals between two dates. For example, we can use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
Syntax:
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
Arguments:
interval
Required Value. A string expression that is the interval you want to use to calculate the differences between date1 and date2. See argument setting section for values.
date1, date2
Required Value. Date expressions. Two dates you want to use in the calculation.
firstdayofweek
Optional Value. Constant value that specifies the day of the week. If not specified, Sunday is assumed. See argument setting section for values.
firstweekofyear
Optional Value. Constant value that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. See argument setting section for values.
Argument Settings:
The interval argument can have the following values:
| Interval Argument Value | Description |
|---|---|
| d | Day |
| m | Month |
| q | Quarter |
| y | Day of year |
| yyyy | Year |
| w | Weekday |
| ww | Week of year |
| h | Hour |
| n | Minute |
| s | Second |
The firstdayofweek argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystemDayOfWeek | 0 | Use National Language Support (NLS) API setting. |
| vbSunday | 1 | Sunday (default) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
The firstweekofyear argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystem | 0 | Use National Language Support (NLS) API setting. |
| vbFirstJan1 | 1 | Start with the week in which January 1 occurs (default). |
| vbFirstFourDays | 2 | Start with the week that has at least four days in the new year. |
| vbFirstFullWeek | 3 | Start with the first full week of the new year. |
Example 1: Calculate number of days difference between two dates.
|
1 2 3 4 5 |
Dim varDateDiff varDateDiff = DateDiff("d", "10/01/2022", "25/01/2022") MsgBox varDateDiff' Will display 15 |
Example 2: Calculate number of month difference between two dates.
|
1 2 3 4 5 |
Dim varMonthDiff varMonthDiff = DateDiff("m", "10/01/2021", "25/01/2022") MsgBox varMonthDiff' Will display 12 |
VBA DatePart Function
The DatePart Function returns the specified part of a given date.
Syntax:
DatePart(interval, date[, firstdayofweek[, firstweekofyear]])
Arguments:
interval
Required Value. A string expression that is the interval you want to use to calculate the differences between date1 and date2. See argument setting section for values.
date
Required Value. A String having Date expression.
firstdayofweek
Optional Value. Constant value that specifies the day of the week. If not specified, Sunday is assumed. See argument setting section for values.
firstweekofyear
Optional Value. Constant value that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. See argument setting section for values.
Argument Settings:
The interval argument can have the following values:
| Interval Argument Value | Description |
|---|---|
| d | Day |
| m | Month |
| q | Quarter |
| y | Day of year |
| yyyy | Year |
| w | Weekday |
| ww | Week of year |
| h | Hour |
| n | Minute |
| s | Second |
The firstdayofweek argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystemDayOfWeek | 0 | Use National Language Support (NLS) API setting. |
| vbSunday | 1 | Sunday (default) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
The firstweekofyear argument can have the following values:
| Constant | Value | Description |
|---|---|---|
| vbUseSystem | 0 | Use National Language Support (NLS) API setting. |
| vbFirstJan1 | 1 | Start with the week in which January 1 occurs (default). |
| vbFirstFourDays | 2 | Start with the week that has at least four days in the new year. |
| vbFirstFullWeek | 3 | Start with the first full week of the new year. |
Example 1: Get month part a particular date.
|
1 2 3 4 5 |
Dim mnthPart mnthPart = DatePart("m", "12/31/2021") MsgBox mnthPart' Will display 12 |
Example 2: Get day part a particular date.
|
1 2 3 4 5 |
Dim dayPart dayPart= DatePart("d", "12/31/2021") MsgBox dayPart' Will display 31 |
Example 3: Get year part a particular date.
|
1 2 3 4 5 |
Dim yearPart yearPart = DatePart("yyyy", "01/31/2022") MsgBox yearPart' Will display 2022 |
VBA DateSerial Function
The VBA DateSerial Function returns a Date by combining the input values year, month, and day.
Syntax:
DateSerial(year, month, day)
Arguments
year
Number between 100 and 9999, inclusive, or a numeric expression.
month
Any numeric expression representing month
day
Any numeric expression representing day
Example: Get date from year, month, and day.
|
1 2 3 4 5 |
Dim varDate varDate = DateSerial("2022", "02","28") MsgBox varDate' Will display date 28-02-2022 as per your system date format |
VBA DateValue Function
The VBA DateValue Function returns a Date when given a string represents a valid date.
Syntax:
DateValue(date)
Arguments:
date
Required Value. A string expression in a format that represents date.
Example 1: Get date from a string representing a date value.
|
1 2 3 4 5 |
Dim myDate myDate = DateValue("August 15, 1947") ' Converts the string into date. MsgBox myDate' Will display date 15-08-1947 as per your system date format |
Example 2: Get date from a string having numeric values representing a date value.
|
1 2 3 4 5 |
Dim myDate myDate = DateValue("15,08,1947") ' Converts the string into date. MsgBox myDate' Will again display date 15-08-1947 as per your system date format |
Example 2: Get date from a string having numeric values representing a date value. In this example day and month are given in the mm/dd format and year is separated by a comma.
|
1 2 3 4 5 |
Dim myDate myDate = DateValue("03/31,2020") ' Converts the string into date. MsgBox myDate' Will display date 31-03-2020 as per your system date format |
Example 2: Get date from a string having numeric values representing a date value. In this example day and month are given in the dd/mm format and year is separated by a comma.
|
1 2 3 4 5 |
Dim myDate myDate = DateValue("25/07,2020") ' Converts the string into date. MsgBox myDate' Will display date 25-07-2020 as per your system date format |
Few other examples of string representing a date are as follows:
- December 15, 2020
- Dec 15, 2020
- 12/30/21
- 11/22/1970
- 22/11/1970
- 25/07 ‘ Will return date with current year if year value not provided
- 07/25 ‘Will return date with current year if year value not provided
VBA Day Function
The VBA Day Function returns a whole number between 1 and 31, that represents the day of the month.
Syntax:
Day(date)
Arguments:
date
Required Value. A string expression in a format that represents date.
Example : Get day part a particular date.
|
1 2 3 4 5 |
Dim iDay iDay = Day("14/06/2018") MsgBox iDay' Will display 14 |
VBA Hour Function
The VBA Hour Function returns the hour of an input time.
Syntax:
Hour(time)
Arguments:
time
Required Value. A string expression in a format that represents time.
Example : Get hour for the given time.
|
1 2 3 4 5 |
Dim iHour iHour = Hour("18:14:17 PM") MsgBox iHour ' Will display 18 |
VBA Minute Function
The Minute Function to returns the minute value of a given time.
Syntax:
Minute(time)
Arguments:
time
Required Value. A string expression in a format that represents time.
Example : Get minutes for the given time.
|
1 2 3 4 5 |
Dim iMinute iMinute = Minute("18:50:17 PM") MsgBox iMinute' Will display 50 |
VBA Second Function
The Second Function to returns the second value of given time.
Syntax:
Second(time)
Arguments:
time
Required Value. A string expression in a format that represents time.
Example : Get seconds for the given time.
|
1 2 3 4 5 |
Dim iSecond iSecond = Second("18:50:17 PM") MsgBox iSecond' Will display 17 |
VBA Month Function
The Month Function returns the month number of given date.
Syntax:
Month(date)
Arguments:
date
Required Value. A string expression in a format that represents date.
Example : Get month for the given date.
|
1 2 3 4 5 |
Dim iMonth iMonth= Month("10/15/2021") MsgBox iMonth' Will display 10 |
VBA MonthName Function
The MonthName Function returns the name of a month from a given month number.
Syntax:
MonthName(month[,abbreviate])
Arguments:
month
Required Value. An integer value between 1 and 12 that represents a month number. For example, January is 1, February is 2, and so on.
abbreviate
Optional Value. Boolean value that indicates if the month name is to be abbreviated. If omitted, the default is False, which means that the month name is not abbreviated.
Example 1: Get month name for the given date.
|
1 2 3 4 5 |
Dim strMonthName strMonthName = MonthName("10") MsgBox "Month Name with abbreviation False is ->: " & strMonthName ' Will display full month name October |
Example 2: Get abbreviated month name for the given date.
|
1 2 3 4 5 |
Dim strMonthName strMonthName = MonthName("10",True) MsgBox "Month Name with abbreviation True is ->: " & strMonthName ' Will display abbreviated month name Oct |
VBA TimeSerial Function
The TimeSerial Function returns time by combining the input values hour, minute and second.
Syntax:
TimeSerial(hour, minute, second)
Arguments
hour
Number between 0 (12:00 A.M.) and 23 (11:00 P.M.) that represents the hour value.
minute
Any numeric expression between 0 and 59 that represents the minute value.
second
Any numeric expression between 0 and 59 that represents the second value.
Example : Get time for the given input values.
|
1 2 3 4 5 |
Dim MyTime MyTime = TimeSerial(10, 50, 30) MsgBox MyTime ' Will display 10:50:30 |
VBA Weekday Function
The VBA Weekday Function returns a whole number representing the day of the week.
Syntax:
Weekday(date, [firstdayofweek])
Arguments
date
Any expression that can represent a date. If date contains Null, Null is returned.
firstdayofweek
A constant that specifies the first day of the week. If omitted, vbSunday is assumed.
The firstdayofweek argument could have following valid values.
| Constant | Value | Description |
|---|---|---|
| vbUseSystemDayOfWeek | 0 | Use National Language Support (NLS) API setting. |
| vbSunday | 1 | Sunday (default) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
Return Values of Weekday Function
| Constant | Value | Description |
|---|---|---|
| vbSunday | 1 | Sunday |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
Example : Get week day for the given input values.
|
1 2 3 4 5 |
Dim MyWeekDay MyWeekDay = Weekday("12/03/2022") MsgBox MyWeekDay' Will display 7 |
VBA WeekdayName Function
The WeekdayName Function returns name of a weekday from provided input weekday number.
Syntax:
WeekdayName(weekday, abbreviate, firstdayofweek)
Arguments
weekday
Required Value. The numeric value between 1 and 7.
abbreviate
Optional Value. Boolean value that indicates if the weekday name is to be abbreviated. If omitted, the default is False, which means that the weekday name is not abbreviated.
firstdayofweek
Optional Value. Numeric value indicating the first day of the week. The firstdayofweek argument could have following valid values.
| Constant | Value | Description |
|---|---|---|
| vbUseSystemDayOfWeek | 0 | Use National Language Support (NLS) API setting. |
| vbSunday | 1 | Sunday (default) |
| vbMonday | 2 | Monday |
| vbTuesday | 3 | Tuesday |
| vbWednesday | 4 | Wednesday |
| vbThursday | 5 | Thursday |
| vbFriday | 6 | Friday |
| vbSaturday | 7 | Saturday |
Example : Get weekday name for the given input value.
|
1 2 3 4 5 |
Dim myWeekDayName myWeekDayName = WeekdayName(6) MsgBox myWeekDayName ' Will display week day name as Friday |
Example : Get abbreviated weekday name for the given input value.
|
1 2 3 4 5 |
Dim myWeekDayName myWeekDayName = WeekdayName(6,True) MsgBox myWeekDayName ' Will display week day name as Fri |
VBA Year Function
The VBA Year Function returns a whole number representing the year.
Syntax:
Year(date)
Arguments:
date
Required Value. A string expression in a format that represents date.
Example : Get year value for the given input date.
|
1 2 3 4 5 |
Dim iYear iYear = Year("10/12/2015") MsgBox iYear ' Will display 2015 |
VBA isDate Function
The isDate Function returns a Boolean value indicating whether an string expression can be converted to a date.
Syntax:
IsDate(expression)
Remarks: The expression argument can be any date expression or string expression recognizable as a date or time.
Example: Validation of various expression to check whether the expressions are a valid Date
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Dim myDateX, myDateY, myDateZ, myDateM, invalidDate, checkDate myDateX = "May 19, 1990" myDateY = #10/15/1990# myDateZ = "12,31,2018" myDateM = "12/31/2018" invalidDate = "Hello 12/12/2012" checkDate = IsDate(myDateX) ' Returns True. MsgBox "myDateX is a Date: " & checkDate checkDate = IsDate(myDateY) ' Returns True. MsgBox "myDateY is a Date: " & checkDate checkDate = IsDate(myDateZ) ' Returns True. MsgBox "myDateZ is a Date: " & checkDate checkDate= IsDate(myDateM) ' Returns True. MsgBox "myDateM is a Date: " & checkDate checkDate = IsDate(invalidDate) ' Returns False. MsgBox "invalidDate is a Date: " & checkDate |
VBA CDate Function
The VBA CDate Function returns an expression after converting it to system Date format.
Syntax:
CDate(expression)
Remarks: This function is quite useful if you have to compare two different dates having same date values but in different date formats. Also, use the IsDate function to determine if an expression can be converted to a date or time before using CDate function.
Example: The following example uses the CDate function to convert a string to a date.
|
1 2 3 4 5 6 7 8 9 10 |
Dim myDate1, myDate2, myDateExpress1, myDateExpress2 myDate1 = "12/31/2019" 'Date in format dd/mm/yyyy myDate2 = "31/12/2019" 'Date in format mm/dd/yyyy myDateExpress1 = CDate(myDate1) ' Converts date into system date format myDateExpress2 = CDate(myDate2) ' Converts date into system date format MsgBox "myDateExpress1 is: " & myDateExpress1 & " and myDateExpress2 is also: " & myDateExpress2 |
Compare Dates in VBA
We can compare two dates in VBA using the >, <, and = operators in the same fashion we do compare numeric values. The following code shows you how to compare two dates in VBA. The following example shows how to compare two dates in VBA.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Dim myDateX, myDateY myDateX = "01/26/2020" myDateY = "02/26/2020" If myDateX = myDateY Then MsgBox "myDateX and myDateY are equal" ElseIf myDateX > myDateY Then MsgBox "myDateX is greater than myDateY" Else MsgBox "myDateY is greater than myDateX" End If |
Custom Date Convert Function
So far we seen all VBA Date functions and I hope, now you can utilize them in appropriately as per the need. Now I will show you how to write our own user-defined function using few of the date functions to convert date to any other date format. This function has been written just for learning purpose. Having said that, VBA already has Format method to format String values to any required format that I will be covering in another post.
Syntax:
ConvertDateFormat(inputDateExpression, outPutDateFormat, dateSeparator)
Arguments:
inputDateExpression : A valid date expression.
outPutDateFormat: The output date format like dd/mm/yy or mm/dd/yyyy and so on. The expected date formats are mentioned in the select case of ConvertDateFormat function. You can further customize this function if required.
dateSeparator: A character to separate date, month and year. Valid values could be / and – .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Function ConvertDateFormat(inputDateExpression, outPutDateFormat, dateSeparator) InputStringToDate = CDate(inputDateExpression) DD = Day(InputStringToDate) If DD < 10 Then DD = "0" & DD End If MM = Month(InputStringToDate) If MM < 10 Then MM = "0" & MM End If YYYY = Year(InputStringToDate) YY = Right(YYYY, 2) Select Case UCase(outPutDateFormat) Case "DDMMYYYY" ConvertDateFormat = DD & MM & YYYY Case "MMDDYYYY" ConvertDateFormat = MM & DD & YYYY Case "DD/MM/YYYY" ConvertDateFormat = DD & dateSeparator & MM & dateSeparator & YYYY Case "MM/DD/YYYY" ConvertDateFormat = MM & dateSeparator & DD & dateSeparator & YYYY Case "MM/DD/YY" ConvertDateFormat = MM & dateSeparator & DD & dateSeparator & YY Case "DD/MM/YY" ConvertDateFormat = DD & dateSeparator & MM & dateSeparator & YY Case "MM/DD" ConvertDateFormat = MM & dateSeparator & DD Case "DD/MM" ConvertDateFormat = DD & dateSeparator & MM Case "MMDD" ConvertDateFormat = MM & DD Case "DDMM" ConvertDateFormat = DD & MM Case "YYYY/MM/DD" ConvertDateFormat = YYYY & dateSeparator & MM & dateSeparator & DD Case Else ConvertDateFormat = False End Select End Function 'Calling the above function in other Function Function TestDtFormatConversion() Dim myDate myDate = "2022/01/31" MsgBox "Converted Date is: " & ConvertDateFormat(myDate, "mm/dd/yyyy", "/") End Function |
The output will be as follows:
Conclusion
I have tried to cover almost VBA date functions in this article. Hope, now you will be having a good understanding of the discussed functions and now you will be able to use them in an efficient way. If you liked this article, please don’t forget to share it and like it.
Recommended Posts
- How To Add Checkpoints in UFT
- Create User-Defined Functions in UFT
- Read, Write and Update Excel File In UFT
- File System Object UFT | VBA
- All You Need to Know About Object Identification in UFT
- Descriptive Programming in UFT with Examples
- How to Use Virtual object in UFT to Identify Non-Standard Class Objects
- DataTable in UFT One | Example of Datatable Methods
