- VBA Tutorial
- VBA - Home
- VBA - Overview
- VBA - Excel Macros
- VBA - Excel Terms
- VBA - Macro Comments
- VBA - Message Box
- VBA - Input Box
- VBA - Variables
- VBA - Constants
- VBA - Operators
- VBA - Decisions
- VBA - Loops
- VBA - Strings
- VBA - Date and Time
- VBA - Arrays
- VBA - Functions
- VBA - Sub Procedure
- VBA - Events
- VBA - Error Handling
- VBA - Excel Objects
- VBA - Text Files
- VBA - Programming Charts
- VBA - Userforms
- VBA Useful Resources
- VBA - Quick Guide
- VBA - Useful Resources
- VBA - Discussion
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
