VBA Date Functions in UFT | Excel

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-in-UFT

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:

VBA-Date-function-get-current-date-in-uft

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:

VBA-Time-function-get-current-Time-in-uft

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:

VBA-Date-Time-function-get-current-Date-and-Time-in-uft

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 ValueDescription
dDay
mMonth
qQuarter
yDay of year
yyyyYear
wWeekday
wwWeek of year
hHour
nMinute
sSecond

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.

VBA-DateAdd-Function

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.

Example 3: Subtract 5 days of the March 5.

Example 4: Add 10 minutes to current time.

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 ValueDescription
dDay
mMonth
qQuarter
yDay of year
yyyyYear
wWeekday
wwWeek of year
hHour
nMinute
sSecond

The firstdayofweek argument can have the following values:

ConstantValueDescription
vbUseSystemDayOfWeek0Use National Language Support (NLS) API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

The firstweekofyear argument can have the following values:

ConstantValueDescription
vbUseSystem0Use National Language Support (NLS) API setting.
vbFirstJan11Start with the week in which January 1 occurs (default).
vbFirstFourDays2Start with the week that has at least four days in the new year.
vbFirstFullWeek3Start with the first full week of the new year.

Example 1: Calculate number of days difference between two dates.

VBA-DateDiff-Function-UFT

Example 2: Calculate number of month difference between two dates.

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 ValueDescription
dDay
mMonth
qQuarter
yDay of year
yyyyYear
wWeekday
wwWeek of year
hHour
nMinute
sSecond

The firstdayofweek argument can have the following values:

ConstantValueDescription
vbUseSystemDayOfWeek0Use National Language Support (NLS) API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

The firstweekofyear argument can have the following values:

ConstantValueDescription
vbUseSystem0Use National Language Support (NLS) API setting.
vbFirstJan11Start with the week in which January 1 occurs (default).
vbFirstFourDays2Start with the week that has at least four days in the new year.
vbFirstFullWeek3Start with the first full week of the new year.

Example 1: Get month part a particular date.

VBA-DatePart-Function-UFT

Example 2: Get day part a particular date.

VBA-DatePart-Function-in-UFT

Example 3: Get year part a particular date.

get-year-part-in-vba-for-a-date-uft

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.

VBA-DateSerial-Function-UFT

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.

VBA-DateValue-Function-UFT

Example 2: Get date from a string having numeric values representing a date value.

VBA-DateValue-Function-UFT

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.

VBA-DateValue-Function-UFT-2

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.

VBA-DateValue-Function-UFT-3

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.

VBA-Day-Function-UFT

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.

VBA-Hour-Function-UFT

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.

VBA-Minute-Function-UFT

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.

VBA-Second-Function-UFT

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.

VBA-Month-Function-UFT

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.

VBA-MonthName-Function-UFT

Example 2: Get abbreviated month name for the given date.

VBA-MonthName-Function-UFT-2

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.

VBA-TimeSerial-Function-UFT

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.

ConstantValueDescription
vbUseSystemDayOfWeek0Use National Language Support (NLS) API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

Return Values of Weekday Function

ConstantValueDescription
vbSunday1Sunday
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

Example : Get week day for the given input values.

VBA-Weekday-Function-UFT

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.

ConstantValueDescription
vbUseSystemDayOfWeek0Use National Language Support (NLS) API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

Example : Get weekday name for the given input value.

VBA-WeekdayName-Function-UFT

Example : Get abbreviated weekday name for the given input value.

VBA-WeekdayName-Function-UFT

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.

VBA-Year-Function-UFT

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

VBA-IsDate-Function-UFT

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.

VBA-CDate-Function-UFT

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.

VBA-Date-Compare-in-UFT

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 .

The output will be as follows:

Date-format-conversion-in-VBA-UFT

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

Leave a Reply