vba date functions

vba date functions

Date function

The Microsoft Excel DATE function returns the current system date.

Syntax

Date()

Example

The DATE function can only be used in VBA code in Microsoft Excel.

Let's look at some Excel DATE function examples and explore how to use the DATE function in Excel VBA code:

Dim currentdate as string

Currentdate=date

Date add function

The Microsoft Excel DATEADD function returns a date after which a certain time/date interval has been added.

Syntax

Dateadd (interval,number,date)

Example

 Dim ldate as date

Ldate=dateadd(“m”,5,”1/11/2018”)

Result=1/4/2019

Date diff function

The Microsoft Excel DATEDIFF function returns the difference between two date values, based on the interval specified.

Syntax

Datediff(“d”,date1,date2)

Example

Dim ldatediff as date

Ldate = datediff(“m”,22/11/2011,1/1/2012)

Result=2

Datepart function

The Microsoft Excel DATEPART function returns a specified part of a given date

Syntax

Datepart(“d”,date)

Example

Dim lvalue as integer

Lvalue = datepart(“d”,22/2/2018)

In this example, the variable called LValue would now contain the value of 22

Date serial function

The  Microsoft Excel DATESERIAL function returns a date given a year, month, and day value.

Syntax

Dateserial(year,month,day)

Example

Dim ldate as date

Ldate=dateserial(2004,5,31)

In this example, the variable called LDate would now contain the value of "5/31/2004".

Date value function

The Microsoft Excel DATEVALUE function returns the serial number of a date.

Syntax

Datevalue(date)

Example

Dim ldate as date

Ldate=datevalue(“may15,2012”)

In this example, the variable called LDate would now contain the value of 5/15/2012.

Day function

The Microsoft Excel DAY function returns the day of the month (a number from 1 to 31) given a date value.

Syntax

Day(date value)

Example

Dim lday as integer

Lday =day(“12/31/2012”)

In this example, the variable called LDay would now contain the value of 31.

Format date function

The Microsoft Excel FORMAT function takes a date expression and returns it as a formatted string.

Syntax

Format (date,format)

Example

Dim lvalue as string

Lvalue  =format(date,”yyyy/mm/dd”)

In this example, the variable called LValue would now contain the date formatted as yyyy/mm/dd.

Hour function

The Microsoft Excel HOUR function returns the hours (a number from 0 to 23) from a time value.

Syntax

Hour(serial-number)

Example

Dim lhour  as integer

Lhour =hour(“10:42:50 pm”)

In this example, the variable called LHour would now contain the value of 22.

Minute function

The Microsoft Excel MINUTE function returns the minutes (a number from 0 to 59) from a time value.

Syntax

Minute(serial-number)

Example

Dim lminute as integer

Lminute = minute(“10/13/58 pm”)

In this example, the variable called LMinute would now contain the value of 13.

Month function

The Microsoft Excel MONTH function returns the month (a number from 1 to 12) given a date value.

Syntax

Month (date value)

Example

Dim lmonth as  integer

Lmonth =month(“12/31/2011”)

In this example, the variable called LMonth would now contain the value of 12.

Month name function

The Microsoft Excel MONTHNAME function returns a string representing the month given a number from 1 to 12.

Syntax

Monthname(number)

Example

Dim  lvalue as string

Lvalue=monthname(3)

In this example, the variable called LValue would now contain the value of 'Mar'.

Now function

The Microsoft Excel NOW function returns the current system date and time.

Syntax

Now()

Example

Dim lvalue as integer

Lvalue = now

In this example, the variable called LValue would now contain the current system date and time.

Time serial function

The Microsoft Excel TIMESERIAL function returns a time given an hour, minute, and second value.

Syntax

Timeserial (hour,minute,second)

Example

The TIMESERIAL function can only be used in VBA code in Microsoft Excel.

Dim ltime as date

Ltime =timeserial(23,5,31)

In this example, the variable called LTime would now contain the value of "11:05:31 PM".

Time value function

The Microsoft Excel TIMEVALUE function returns the serial number of a time.

Syntax

Timevalue(time)

Example

The TIMEVALUE function can also be used in VBA code in Microsoft Excel.

Dim lvalue as date

Ltime = timevalue(“18:30:12”)

In this example, the variable called LTime would now contain the value of 6:30:12 PM.

Weekday function

The Microsoft Excel WEEKDAY function returns a number representing the day of the week, given a date value.

Syntax

Weekday (“date”)

Example

The WEEKDAY function can also be used in VBA code in Microsoft Excel.

Dim lweekday as integer

Lweekday=weekday(“12/31/2001”)

In this example, the variable called LWeekday would now contain the value of 2.

Weekdayname function

The Microsoft Excel WEEKDAYNAME function returns a string representing the day of the week given a number from 1 to 7.

Syntax

Weekdayname(number)

Example

Dim lvalue as string

Lvalue  =weekdayname(3)

In this example, the variable called LValue would now contain the value of 'Wed'.

Year function

The Microsoft Excel YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value.

Syntax

Year(date_value)

Example

The YEAR function can also be used in VBA code in Microsoft Excel.

Dim lyear as integer

Lyear = year(“12/3/2011”)

In this example, the variable called LYear would now contain the value of 2001.