Excel VBA Date & Time Functions; Year, Month, Week & Day Functions
—————————————————–
Contents:
Using Find Method to Search for a Date
—————————————————–
Working with dates and times in Excel VBA can be tricky. There are a number of ways to represent dates in Excel. It is important to ensure that the Date will actually remain the date value which you actually mean to refer or use in your code. Date format is dependent on the regional and date-specific settings of your system / computer, and lack of understanding in using date functions can result in varying formats or incorrect interpretations. Excel VBA Date Functions are used in your code to work with and manipulate dates & times; to validate date values; convert serial numbers or strings to date formats; extract date parts like day, week, month & year; add and subtract date and time intervals; use the current date, the current time, or the day of the week; and so on.
We have explained in detail various aspects of using Dates & Times in Excel VBA, and also the Format Function to use Predefined Named Formats & to create User-Defined Formats, in our separate section of “Excel VBA Dates & Time, Format Function, User Defined Date, Number & String Formats”. In this section we deal with some important Excel VBA Date Functions.
The VBA DateSerial Function returns (Date value) a date for a specified year, month and day. Syntax: DateSerial(year, month, day). All 3 arguments are necessary to specify. Year argument is an Integer, can be a number within the range 100 and 9999, or can be a numeric expression. Month argument is an Integer, can be a number within a valid range of 1 and 12, or can be a numeric expression. Day argument is an Integer, can be a number within a valid range of 1 and 31, or can be a numeric expression. A numeric expression can also be used to specify relative dates for each argument, say, to represent a number of years or months or days before or after a specific date.
When values supplied for an argument exceeds its acceptable range, the increment will be to the next applicable unit, for example, supplying 14 as the value for the month argument will increment to the second month (February) of the next year. The Function will return an error in the case of any argument value being outside the range -32,768 to 32,767 or in case the final date (after evaluating the 3 arguments) falls outside the valid date range.
The supplied values for the year, month & day arguments are assumed to be Gregorian or Hijri, as per the Calendar property setting if Gregorian or Hijri. The Function returns the date part in the time period units of the current Calendar, so that if the date part to be returned is the year, the year value is a Gregorian year where the current Calendar is Gregorian.
It is advisable to use a four-digit year to ensure returning the correct Date by using this Function. Earlier versions of Windows interpret two-digit years based on certain defaults: Specifying two-digit years for the year argument are read as per user-defined desktop settings in Windows 98 or Windows 2000 systems, wherein the default settings read values from 0 to 29 as the years 2000 to 2029 and values from 30 to 99 as the years 1930 to1999 and for all other years the four-digit year is used.
Sub DateSerialFunc_1()
Dim MyDate As Variant
MyDate = DateSerial(2012, 5, 8)
‘returns “5/8/2012”
MsgBox MyDate
‘returns “May 08, 2012”
MsgBox Format(MyDate, “mmmm dd, yyyy”)
‘specify relative dates for an argument, using a numeric expression
‘returns “5/17/2010” (2 years before and 9 days later than the specified date of “5/8/2012”)
MsgBox DateSerial(2012 – 2, 5, 8 + 9)
End Sub
Sub DateSerialFunc_2()
Dim MyDate As Variant
‘Date increments to the second month (February) of the next year
MyDate = DateSerial(2012, 14, 8)
‘returns “2/8/2013”
MsgBox MyDate
‘returns “February 08, 2013”
MsgBox Format(MyDate, “mmmm dd, yyyy”)
‘—————-
‘Date will increment to 5th of next month (May is a 31 day month: 35-31 = 4)
MyDate = DateSerial(2012, 5, 35)
‘returns “6/4/2013”
MsgBox MyDate
‘returns “June 04, 2013”
MsgBox Format(MyDate, “mmmm dd, yyyy”)
‘—————-
‘Date will increment to 5th of next month (June is a 30 day month: 35-30 = 5)
MyDate = DateSerial(2012, 6, 35)
‘returns “7/5/2013”
MsgBox MyDate
‘returns “July 05, 2013”
MsgBox Format(MyDate, “mmmm dd, yyyy”)
‘—————-
‘gives a run time error – value for any argument is outside the range -32,768 to 32,767:
MyDate = DateSerial(2012, 32768, 5)
‘—————-
‘gives a run time error – final date (per the 3 arguments) falls outside the valid date range:
MyDate = DateSerial(9999, 16, 5)
End Sub
Use the DateValue function to convert a string to a Date (Date value). Syntax: DateValue(date). The date argument can be a string expression representing a date, or any expression representing a date or time or both. The expression should represent a date within the range January 1, 100 to December 31, 9999.
Refer below example which illustrates in detail on using the DateValue Function.
Sub DateValueFunc()
‘VBA DateValue Function
Dim vDate As Variant
‘you can assign a date literal to a Variant or Date variable
vDate = #7/5/2012#
‘returns “7/5/2012”
MsgBox DateValue(vDate)
‘————–
‘DateValue Function returns only the Date part (and not Time part) of an expression
Dim MyDate As Date
‘returns “8/13/2013 11.06.25 AM”
MyDate = Format(Now, “mmmm dd, yyyy hh:mm:ss“)
MsgBox MyDate
‘returns “8/13/2013”
MyDate = Format(DateValue(Now), “mmmm dd, yyyy hh:mm:ss“)
MsgBox MyDate
‘returns “August 13, 2013 00:00:00”
MsgBox Format(DateValue(“08/13/2013 11.06.25 AM”), “mmmm dd, yyyy hh:mm:ss”)
‘————–
‘for a string that includes only numbers, and which is separated by valid date separators, your System’s Short Date format determines the sequence for month, day & year for the DateValue Function.
‘for a system Short Date format of “m/d/yyyy” – returns “4/11/2013” meaning “April 11, 2013”
MsgBox DateValue(“4/11/2013”)
‘returns “April 11, 2013”
MsgBox Format(DateValue(“4/11/2013”), “mmmm dd, yyyy”)
‘for a system Short Date format of “m/d/yyyy” – returns “11/4/2013” meaning “November 04, 2013”
MsgBox DateValue(“11/4/2013”)
‘returns “November 04, 2013”
MsgBox Format(DateValue(“11/4/2013”), “mmmm dd, yyyy”)
‘————–
‘Month names, in both long or abbreviated forms, are also recognized by the DateValue function.
‘returns “11/4/2013”
MsgBox DateValue(“Nov 4, 13”)
‘————–
‘if the year is omitted, the current year is considered as per the system date in your computer.
‘returns “4/25/2013”, if the current year is 2013
MsgBox DateValue(“4/25”)
‘————–
‘DateValue Function returns an error for an invalid format
vDate = #4:01:26 PM#
‘returns 12:00:00 AM” (DateValue Function returns only the Date part, and not Time part)
MsgBox DateValue(vDate)
vDate = “36:01:26“
‘returns an error because of invalid time format
MsgBox DateValue(vDate)
End Sub
The VBA TimeSerial Function returns a Time (Date value) for a specified hour, minute & second. Syntax: TimeSerial(hour, minute, second). All 3 arguments are necessary to specify.The hour argument is an Integer, a number within a valid range of 0 and 23, and can be any numeric expression. The minute argument is an Integer, a number within a valid range of 0 and 59, and can be any numeric expression. The second argument is an Integer, a number within a valid range of 0 and 59, and can be a numeric expression. A numeric expression can also be used to specify relative times for each argument, say, to represent a number of hours or minutes or seconds before or after a specific time.
When values supplied for an argument exceeds its acceptable range, the increment will be to the next applicable unit, for example, supplying 70 as the value for the minute argument will increment to the ten minutes of the next hour. The Function will return an error in the case of any argument value being outside the range -32,768 to 32,767 or in case the final date (after evaluating the 3 arguments) falls outside the valid date range.
Refer below example which illustrates in detail on using the TimeSerial Function.
Sub TimeSerialFunc()
Dim vTime As Variant
‘—————-
vTime = TimeSerial(14, 5, 18)
‘returns “2:05:18 PM” – as per your system’s Long Time Format setting – “h:mm:ss AM/PM“
‘changing your system’s Long Time Format setting to 24-hr format of “h:mm:ss” will return the time as “14:05:18”
MsgBox vTime
‘user-defined Time format with VBA Format Function – returns “02:5:18 P”
MsgBox Format(vTime, “hh:n:ss A/P”)
‘—————-
‘value of 85 supplied for minutes argument exceeds its acceptable range of 0 to 59, hence 85 will be evaluated as 1 hr 25 minutes thus incrementing time to the next hour
vTime = TimeSerial(14, 85, 18)
‘returns “3:25:18 PM”
MsgBox vTime
‘—————-
‘using a numeric expression to specify relative times for each argument:
Dim vHr As Variant, vMin As Variant, vSec As Variant
‘returns “7:08:24 PM”
MsgBox TimeSerial(19, 8, 24)
vHr = 3
vMin = 21
vSec = 4
‘returns “4:29:20 PM”
MsgBox TimeSerial(19 – vHr, 8 + vMin, 24 – vSec)
‘—————-
‘gives a run time error – value for any argument is outside the range -32,768 to 32,767:
MsgBox TimeSerial(19, 32768, 5)
End Sub
Use the VBA TimeValue function to convert a string to a time (Date value). Syntax: TimeValue(time) . The time argument can be a string expression representing a time, or any expression representing a time. The expression should represent a time within the range 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive.
Refer below example which illustrates in detail on using the TimeValue Function.
Sub TimeValueFunc()
‘VBA TimeValue Function
Dim vTime As Variant
‘————–
‘you can assign a time literal to a Variant or Date variable
vTime = #5:06:25 PM#
‘returns “5:06:25 PM”
MsgBox TimeValue(vTime)
‘————–
‘time values using a 12-hour or 24-hour format can be entered
‘returns “5:06:25 AM”
MsgBox TimeValue(“05:06:25”)
‘returns “5:06:25 PM”
MsgBox TimeValue(“17:06:25”)
‘————–
‘TimeValue Function returns only the Time part (and not Date part) of an expression
Dim MyTime As Date
‘returns “8/13/2013 11.06.25 AM”
MyTime = Format(Now, “mmmm dd, yyyy hh:mm:ss“)
MsgBox MyTime
‘returns “11.06.25 AM”
MyTime = Format(TimeValue(Now), “mmmm dd, yyyy hh:mm:ss“)
MsgBox MyTime
‘————–
‘TimeValue Function returns an error for an invalid format
vTime = “36:01:26“
‘returns an error because of invalid time format
MsgBox TimeValue(vTime)
End Sub
Example: Extract Date Part and Time Format from a cell value – use DateValue & TimeValue Functions
Sub DateValueTimeValue()
‘Extract Date Part and Time Format from a cell value (cell A1 contains the value 41463.251).
Dim MyDateTime As Date, MyDate As Date, MyTime As Date
Dim strDate As String, strTime As String
‘——————-
‘OPTION 1:
‘It is necessary to convert the expression (cell value) to a String or Date to use the DateValue / TimeValue functions.
‘cell A1 contains the value 41463.251 – Format function converts to a string value.
strDate = Format(Range(“A1”), “mm/dd/yyyy“)
strTime = Format(Range(“A1”), “hh:mm:ss AMPM“)
‘convert string to a Date by using DateValue & TimeValue functions which extract Date & Time parts
MyDate = DateValue(strDate)
MyTime = TimeValue(strTime)
‘returns “7/8/2013”
MsgBox MyDate
‘returns “6:01:26 AM”
MsgBox MyTime
MyDateTime = MyDate + MyTime
‘returns “7/8/2013 6:01:26 AM”
MsgBox MyDateTime
‘——————-
‘OPTION 2:
‘cell A1 contains the value 41463.251
‘returns “7/8/2013 6:01:26 AM”, in date format
MyDateTime = Range(“A1“).Value
MsgBox MyDateTime
‘returns “7/8/2013”
MyDate = DateValue(MyDateTime)
MsgBox MyDate
‘returns “6:01:26 AM”
MyTime = TimeValue(MyDateTime)
MsgBox MyTime
‘——————-
‘OPTION 3:
‘cell A1 contains the value 41463.251
‘returns “7/8/2013 6:01:26 AM”, in date format
MyDateTime = Range(“A1“).Value
MsgBox MyDateTime
‘returns “7/8/2013”
MyDate = MyDateTime – TimeValue(MyDateTime)
MsgBox MyDate
‘returns “6:01:26 AM”
MyTime = MyDateTime – DateValue(MyDateTime)
MsgBox MyTime
End Sub
The VBA IsDate Function is used to check if an expression is a Date or the expression can be converted to a valid Date or Time – the function returns True in this case, else False is returned. Syntax: IsDate(expression). Valid date can range from January 1, 100 to December 31, 9999 in Microsoft Windows operating system (will vary for different systems).
Sub IsDateFunc()
Dim var As Variant
var = “12/18/2011“
‘returns True (valid date)
MsgBox IsDate(var)
var = “18/12/2011“
‘returns True (valid date)
MsgBox IsDate(var)
var = “18/15/2011“
‘returns False (invalid date)
MsgBox IsDate(var)
var = “24 Jan, 2001“
‘returns True (valid date)
MsgBox IsDate(var)
var = “June 31, 2001“
‘returns False (invalid date)
MsgBox IsDate(var)
var = “18.12.11“
‘returns True (valid Time)
MsgBox IsDate(var)
var = “18.12.2011“
‘returns False (invalid Time)
MsgBox IsDate(var)
var = 21345
‘returns False (invalid Date/Time)
MsgBox IsDate(var)
End Sub
Use the VBA CDate function to convert a value to a date (Date value). Syntax: CDate(expression). Refer below examples which explain the CDate Function in detail.
Sub CDateFunc_1()
‘CDate sets date format based on your system’s locale setting
‘returns “3/12/2013”
MsgBox CDate(41345)
‘returns “6:01:26 AM”
MsgBox CDate(0.251)
End Su
Sub CDateFunc_2()
Dim strDate As String, strTime As String
Dim vDate As Variant, vTime As Variant
strDate = “September 24, 1984“
strTime = “17:42:36“
‘set vDate to a Date value.
vDate = CDate(strDate)
‘returns “September 24, 1984”
MsgBox strDate
‘returns “9/24/1984” (CDate sets date format based on your system’s locale setting)
MsgBox vDate
‘set vTime to Date value.
vTime = CDate(strTime)
‘returns “17:42:36”
MsgBox strTime
‘returns “5:42:36 PM”
MsgBox vTime
End Sub
Sub InputBoxDateFormat()
‘date format with InputBox
Dim strDate As String
strDate = InputBox(“Enter date“)
‘Use the IsDate function to determine if a value can be converted to a date and time.
If IsDate(strDate) Then
‘CDate function converts a value to a date. CDate recognizes date formats based on your system’s locale setting. Ensure that you provide the day, month, and year in the correct sequence or order per your locale setting, or the date might not be correctly interpreted. A long date format containing a string value specifying a weekday like ‘Tuesday’ will not be recognized.
strDate = Format(CDate(strDate), “dd/mm/yyyy“)
‘displays date in the format of day-month-year
MsgBox strDate
Else
MsgBox “Invalid date”
End If
End Sub
Use the VBA DateAdd Function to add or subtract a specified time interval to a Date – it returns (Date value) a future or past Date after adding or subtracting the specified time interval. Syntax: DateAdd(interval, number, date). All 3 arguments are necessary to specify. Interval argument is a String value, specifying the time interval you wish to add or subtract. Number argument is a numeric specifying the number of time intervals – use a positive number for future dates and a negative to get past dates. Date argument specifies the Date (Variant) to which you want to add or subtract the time interval.
Settings for the interval argument: yyyy – Year; q – Quarter; m – Month; y – Day of Year; d – Day; w – Weekday; ww – Week; h – Hour; n – Minute; s – Second.
Note that the Date returned by the function is as per your system-defined date format, and not by the format used in date argument.
Sub DateAddFunc()
Dim vDate As Variant
Dim strInterval As String
‘—————————–
‘use y – Day of Year, d – Day or w – Weekday to add days to a date.
vDate = #8/11/2013#
strInterval = “y“
‘returns “8/16/2013”
MsgBox DateAdd(strInterval, 5, vDate)
strInterval = “d“
‘returns “8/16/2013”
MsgBox DateAdd(strInterval, 5, vDate)
strInterval = “w“
‘returns “8/16/2013”
MsgBox DateAdd(strInterval, 5, vDate)
‘—————————–
vDate = #1/25/2004#
‘returns “2/25/2004”
MsgBox DateAdd(“m”, 1, vDate)
vDate = #1/31/2004#
‘returns “2/29/2004” (2004 is a leap year)
MsgBox DateAdd(“m”, 1, vDate)
vDate = #1/31/2004#
‘returns “2/28/2004”
MsgBox DateAdd(“ww”, 4, vDate)
vDate = #1/25/2004#
‘returns “7/25/2004” – rounds off the number argument to the nearest whole number, if not a Long value.
MsgBox DateAdd(“q”, 2.2, vDate)
vDate = #1/25/2004#
‘returns “7/25/2004” – rounds off the number argument to the nearest whole number, if not a Long value.
MsgBox DateAdd(“q”, 2.2, vDate)
‘Note that the Date returned by the function is as per your system-defined date format, and not by the format used in date argument.
‘returns “7/27/1994”.
MsgBox DateAdd(“yyyy”, -2, “July 27, 1996”)
‘returns “2:23:12 PM”.
MsgBox DateAdd(“n”, -5, “14:28:12”)
‘returns “1:23:12 PM”.
MsgBox DateAdd(“n”, -65, “14:28:12”)
‘the Function will return an error, if the calculated date is not within the valid date range.
‘returns an error because the year precedes 100:
MsgBox DateAdd(“yyyy”, -1900, “No 27, 1996”)
End Sub
Use the VBA DateDiff Function to return the number (Long) of time intervals between two dates. Syntax: DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear). The arguments of interval, date1 & date2 are necessary to specify, while it is optional to specify firstdayofweek & firstweekofyear arguments. The interval argument is a String value, specifying the time interval which is used to calculate the date1 & date2 difference. Date1 & date2 are the two dates whose difference is being calculated. The firstdayofweek argument is a constant, specifying the first day of the week – Default is Sunday. The firstweekofyear argument is a constant, specifying the first week of the year – Default is the week containing January 1.
Settings for the interval argument: yyyy – Year; q – Quarter; m – Month; y – Day of Year; d – Day; w – Weekday; ww – Week; h – Hour; n – Minute; s – Second.
Constants to be used for the argument firstdayofweek: vbUseSystem (value 0) – use NLS API setting; vbSunday (this is the default) (value 1) – Sunday; vbMonday (value 2) – Monday; vbTuesday (value 3) – Tuesday; vbWednesday (value 4) – Wednesday; vbThursday (value 5) – Thursday; vbFriday (value 6) – Friday; vbSaturday ( value 7) – Saturday.
Constants to be used for the argument firstweekofyear: vbUseSystem (value 0) – use NLS API setting; vbFirstJan1 (this is the default) (value 1) – start with week in which January 1 occurs; vbFirstFourDays (value 2) – start with the first week which has at least four days in the year; vbFirstFullWeek (value 3) – start with the first full week of the year which has all 7 days.
Where date1 is later than date2, a negative value is returned by the DateDiff function.
Refer below examples which explain the DateDiff Function in detail.
Sub DateDiffFunc_1()
‘CALCULATE NO. OF DAYS BETWEEN TWO DATES
Dim vDate1 As Variant, vDate2 As Variant
Dim strInterval As String
‘use y – Day of Year or d – Day, to calculate no. of days between two dates.
vDate1 = #2/25/2004#
vDate2 = #3/5/2004#
strInterval = “y“
‘calculate number of days between two dates: returns 9 (2004 is leap year)
MsgBox DateDiff(strInterval, vDate1, vDate2)
strInterval = “d“
‘calculate number of days between two dates: returns -9 (2004 is leap year)
‘Where first date is later than second date, a negative value is returned by the DateDiff function.
MsgBox DateDiff(strInterval, vDate2, vDate1)
End Sub
Sub DateDiffFunc_2()
‘CALCULATE NO. OF WEEKS BETWEEN TWO DATES
Dim vDate1 As Variant, vDate2 As Variant
Dim strInterval As String
‘vDate1 is a Tuesday
vDate1 = #3/5/2013#
‘vDate2 is a Sunday
vDate2 = #3/17/2013#
‘returns Tuesday
MsgBox Format(vDate1, “dddd”)
‘returns Sunday
MsgBox Format(vDate2, “dddd”)
strInterval = “w“
‘calculate number of weeks between two dates – returns 1
‘vDate1 is a Tuesday, and when using interval of “w” (Weekday) DateDiff will count number of Tuesdays till vDate2
MsgBox DateDiff(strInterval, vDate1, vDate2)
strInterval = “ww“
‘calculate number of weeks between two dates – returns 2
‘when using interval of “ww” (Week) DateDiff counts number of calendar weeks (ie. the number of Sundays) from vDate1 till vDate2
MsgBox DateDiff(strInterval, vDate1, vDate2)
strInterval = “ww“
‘calculate number of weeks between two dates – returns 1
‘when using interval of “ww” (Week) DateDiff counts number of calendar weeks (ie. the number of Sundays) from vDate1 till vDate2
‘the firstdayofweek argument can affect the calculation when using interval of “ww” ((Week)- the Sunday falling on March 10, 2013 will not be counted below.
MsgBox DateDiff(strInterval, vDate1, vDate2, vbMonday)
‘vDate1 is a Sunday
vDate1 = #3/10/2013#
‘vDate2 is a Sunday
vDate2 = #3/17/2013#
strInterval = “ww“
‘calculate number of weeks between two dates (both vDate1 & vDate2 are Sundays) – returns 1
‘using interval of “ww” (Week), DateDiff counts number of Sundays from vDate1 till vDate2 – it counts vDate2 if it falls on a Sunday but not vDate1 even if it falls on a Sunday.
MsgBox DateDiff(strInterval, vDate1, vDate2)
End Sub
Sub DateDiffFunc_3()
‘CALCULATE NO. OF MONTHS ELAPSED FROM A SPECIFIC DATE TILL TODAY
Dim vDate1 As Variant, vDate2 As Variant
Dim strInterval As String
vDate1 = #8/25/2012#
‘current date
vDate2 = Now
MsgBox “Months elapsed from ” & Format(vDate1, “mmmm d, yyyy”) & ” till today are: ” & DateDiff(“m”, vDate1, vDate2)
End Sub
Sub DateDiffFunc_4()
‘CALCULATE NO. OF QUARTERS / YEARS BETWEEN TWO DATES
Dim vDate1 As Variant, vDate2 As Variant
Dim strInterval As String
vDate1 = #12/31/2012#
vDate2 = #1/1/2013#
strInterval = “q“
‘calculate number of quarters between two dates: returns 1, even though the difference between dates is of 1 day
MsgBox DateDiff(strInterval, vDate1, vDate2)
strInterval = “yyyy“
‘calculate number of years between two dates: returns 1, even though the difference between dates is of 1 day
MsgBox DateDiff(strInterval, vDate1, vDate2)
End Sub
Sub DateDiffFunc_5()
‘If both dates are entered as ‘date literal’, the year specified therein is used for calculation in the DateDiff function. But if the dates are enclosed in double quotation marks (“) & the year is omitted, the dates are evaluated by inserting the current year in your code.
Dim vDate1 As Variant, vDate2 As Variant
Dim strInterval As String
vDate1 = #2/25/2004#
vDate2 = #3/5/2004#
strInterval = “d“
‘calculate number of days between two dates – returns 9 (2004 is leap year)
MsgBox DateDiff(strInterval, vDate1, vDate2)
‘dates are enclosed in double quotation marks (“) & the year is omitted
vDate1 = “2/25“
vDate2 = “3/5“
strInterval = “d“
‘calculate number of days between two dates – returns 8 (dates are evaluated by inserting the current year, which is not a leap year)
MsgBox DateDiff(strInterval, vDate1, vDate2)
End Sub
Use the VBA DatePart Function to return (Integer) a specified part of a date. Syntax: DatePart(interval, date, firstdayofweek, firstweekofyear). The arguments of interval & date are necessary to specify, while it is optional to specify firstdayofweek & firstweekofyear arguments. The interval argument is a String value – it is the time interval you want to return from the date being evaluated. The date argument is the Date value which is evaluated and whose time interval is to be returned. The firstdayofweek argument is a constant, specifying the first day of the week – Default is Sunday. The firstweekofyear argument is a constant, specifying the first week of the year – Default is the week containing January 1.
Settings / Constants for the interval, firstdayofweek & firstweekofyear arguments are the same as in the VBA DateDiff Function explained above.
Refer below example which explains the DatePart Function in detail.
Sub DatePartFunc()
‘VBA DatePart Function
Dim vDate As Variant
Dim strInterval As String
vDate = #2/25/2004#
strInterval = “y“
‘returns 56 – the Day of year
MsgBox DatePart(strInterval, vDate)
strInterval = “d“
‘returns 25 – the Day part of the date
MsgBox DatePart(strInterval, vDate)
‘returns 28 – the minute part of the Time
MsgBox DatePart(“n”, “14:28:12”)
‘returns 14 – the hour part of the Time
MsgBox DatePart(“h”, “2:28:12 PM”)
‘returns 3 – the Weekday in date (#3/5/2013# is Tuesday), Sunday being the first day of week
MsgBox DatePart(“w”, #3/5/2013#)
‘the firstdayofweek argument can affect the calculation when using interval of “w” ((Weekday)
‘returns 2 – the Weekday in date (#3/5/2013# is Tuesday), Monday being the first day of week
MsgBox DatePart(“w”, #3/5/2013#, vbMonday)
‘returns the Week in date, considering Sunday to be the first day of week
‘returns 2 – second week starts from “1/6/2013” which is a Sunday
MsgBox DatePart(“ww”, #1/7/2013#)
‘the firstdayofweek argument can affect the calculation when using interval of “ww” ((Week)
‘returns the Week in date, considering Tuesday to be the first day of week
‘returns 1 – second week starts from “1/8/2013” which is a Tuesday
MsgBox DatePart(“ww”, #1/7/2013#, vbTuesday)
‘If both dates are entered as ‘date literal’, the year specified therein is used for calculation in the DateDiff function. But if the dates are enclosed in double quotation marks (“) & the year is omitted, the dates are evaluated by inserting
the current year in your code.
‘returns 61 – day of year is evaluated for the year 2004 which is leap year
MsgBox DatePart(“y”, #3/1/2004#)
‘returns 60 – day of year is evaluated by inserting the current year, which is not a leap year
MsgBox DatePart(“y”, “3/1”)
End Sub
The VBA Date function returns (Date value) the current system date. VBA Code: MsgBox Date – returns the current system date (ex. “8/11/2013”)
The VBA Now function returns (Date value) the current system date and time. VBA Code: MsgBox Now – returns the current system date & ime (ex. “8/11/2013 2:26:32 PM”)
Use the VBA MonthName Function to return (String) the month name from a given number. Syntax: MonthName(month, abbreviate). The month argument is necessary to specify, and it is a numeric representing a month, ex. January is 1, February is 2, and so on. The abbreviate argument is optional, it is a Boolean value wherein False (default) indicating that the month name should not be abbreviated. VBA Code: MsgBox MonthName(8, True) – returns “Aug”.
Use the VBA Day Function to return (Integer) the day of the month from a given date – it returns a whole number between 1 and 31. Syntax: Day(date). The date argument can be a Variant, or a numeric or string expression, representing a valid date. VBA Code: MsgBox Day(“August 24, 2012”) – returns “24”.
Use the VBA Month Function to return (Integer) the month of the year from a given date – it returns a whole number between 1 and 12. Syntax: Month(date). The date argument can be a Variant, or a numeric or string expression, representing a valid date. VBA Code: MsgBox Month(“August 24, 2012”) – returns “8”.
Use the VBA Year Function to return (Integer) the year from a given date – it returns a whole number. Syntax: Year(date). The date argument can be a Variant, or a numeric or string expression, representing a valid date. VBA Code: MsgBox Year(“August 24, 2012”) – returns “2012”.
Use the VBA Hour Function to return (Integer) the hour from a given time – it returns a whole number between 0 and 23. Syntax: Hour(time). The time argument can be a Variant, or a numeric or string expression, representing a valid time. VBA Code: MsgBox Hour(“2:04:36 PM”) – returns “14”.
Use the VBA Minute Function to return (Integer) the minute of the hour from a given time – it returns a whole number between 0 and 59. Syntax: Minute(time). The time argument can be a Variant, or a numeric or string expression, representing a valid time. VBA Code: MsgBox Minute(“2:04:36 PM”) – returns “4”.
Use the VBA Second Function to return (Integer) the second of the minute from a given time – it returns a whole number between 0 and 59. Syntax: Second(time). The time argument can be a Variant, or a numeric or string expression, representing a valid time. VBA Code: MsgBox Second(“2:04:36 PM”) – returns “36”.
Use the VBA Weekday Function to return (Integer) the day of the week from a given date – it returns a whole number. Syntax: Weekday(date, firstdayofweek). The date argument is necessary to specify, and it can be a Variant, or a numeric or string expression, representing a valid date. The firstdayofweek argument (optional) is a constant, specifying the first day of the week – Default is Sunday.
Constants to be used for the argument firstdayofweek: vbUseSystem (value 0) – use NLS API setting; vbSunday (this is the default) (value 1) – Sunday; vbMonday (value 2) – Monday; vbTuesday (value 3) – Tuesday; vbWednesday (value 4) – Wednesday; vbThursday (value 5) – Thursday; vbFriday (value 6) – Friday; vbSaturday (value 7) – Saturday.
Values returned by the VBA Weekday Function (Constant – value – Day): vbSunday – 1 – Sunday; vbMonday – 2 – Monday; vbTuesday – 3 – Tuesday; vbWednesday – 4 – Wednesday; vbThursday – 5 – Thursday; vbFriday – 6 – Friday; vbSaturday – 7 – Saturday.
VBA Code: MsgBox Weekday(#8/24/2012#) – returns “6” (representing a Friday)
VBA Code: MsgBox Weekday(#8/24/2012#, vbMonday) – returns “5” (representing a Friday – first day of week is set as Monday instead of Sunday)
Use the VBA WeekdayName Function to return the name of the weekday – it returns a String. Syntax: WeekdayName(weekday, abbreviate, firstdayofweek). The weekday argument is necessary to specify – it is a numeric value representing the day of the week, depending on the firstdayofweek setting. The abbreviate argument (optional) is a Boolean value wherein False (default) indicates that the weekday name should not be abbreviated. The firstdayofweek argument (optional) is a numeric value, specifying the first day of the week – Default is Sunday.
Values for the argument firstdayofweek: vbUseSystem (value 0) – use NLS API setting; vbSunday (this is the default) (value 1) – Sunday; vbMonday (value 2) – Monday; vbTuesday (value 3) – Tuesday; vbWednesday (value 4) – Wednesday; vbThursday (value 5) – Thursday; vbFriday (value 6) – Friday; vbSaturday (value 7) – Saturday.
VBA Code: MsgBox WeekdayName(6) – returns “Friday”
VBA Code: MsgBox WeekdayName(6, True) – returns “Fri”
VBA Code: MsgBox WeekdayName(6, False, vbMonday) – returns “Saturday”, first day of week is set as Monday instead of Sunday
Using Find Method to Search for a Date
Using a Valid Excel Date and Format: Using the Find Method to Find or Search a Date can be tricky. The date format should correspond to the default date format as set in your desktop/Windows which, unless specifically changed, should be in its standard format of “Short Date” or “Long Date’, viz. “1/22/2010” or “January 22, 2010”. It does not matter in which date format it is displayed in the worksheet, only it should be a valid Excel date equating to a valid serial number.
Example – Search for a date within a range – refer Image 1.
Sub FindMethod_SearchDate()
‘Search for a date within a range – refer Image 1.
‘user enters the date he wants to find in Range (“D2”) – he enters the serial number 41200 in this cell;
‘user wants to find the date in search Range (“A1:A100”) – the range “$A$5” is returned in this example because the serial number 41200 corresponds to the date 18-Oct-12 which is entered in cell A5.
Dim rngFound As Range, rngSearch As Range, rngLast As Range
Dim strDate As String
‘search range to find the date:
Set rngSearch = ActiveSheet.Range(“A1:A100“)
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
‘user enters the date he wants to find in Range: ActiveSheet.Range(“D2”).
‘Format(“7/18/10”, “Short Date”) returns “7/18/2010”; Format(“7/18/10”, “Long Date”) returns “Sunday, July 18, 2010”.
‘Format Function in vba: Display a date according to your system’s short date format using “Short Date”; and using “Long Date” displays a date according to your system’s long date format.
strDate = Format(ActiveSheet.Range(“D2“), “Short Date”)
‘The IsDate function returns True if the expression is a valid date, else it returns False.
If IsDate(strDate) = False Then
MsgBox “Incorrect Date Format”
Exit Sub
End If
‘CDate converts a number or text string to a Date data type. CDate(40200) returns “1/22/2010”; CDate(“October 15, 2009”) returns “10/15/2009”; CDate(“2:25:15 PM”) returns “2:25:15 PM”; CDate(“hello”) returns an error.
Set rngFound = rngSearch.Find(What:=CDate(strDate), After:=rngLast, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rngFound Is Nothing Then
‘return range address ($A$5 – in this example) if date is found:
MsgBox rngFound.Address
Else
‘if date is not found in search range:
MsgBox “Date Not Found”
End If
End Sub