Be the first user to complete this post
|
Add to List |
VBA-Excel: Date-Time Functions – DateDiff()
Description:
The DateDiff() function returns the specified time interval difference between the two dates provided.
Format:
DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])
Arguments:
- interval
- Mandatory
- Type: String expression
- The time interval you want the difference between two dates.
Setting | Description |
Yyyy | Year |
Q | Quarter |
M | Month |
Y | Day of year |
D | Day |
W | Weekday |
Ww | Week |
H | Hour |
N | Minute |
S | Second |
- Date1
- Mandatory
- Type: Date
- Date1, one of the dates for which difference is to be calculated
- Date2
- Mandatory
- Type: Date
- Date2, one of the dates for which difference is to be calculated
- Firstdayofweek
- Optional
- Type: Numeric, Text
- Specified the first day of the week, default : Sunday
Constant | Value | Description |
vbUseSystem | 0 | Use the 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 |
- Firstweekofyear
- Optional
- Type: Numeric, Text
- Specified the first week of the week.
Constant | Value | Description |
vbUseSystem | 0 | Use the NLS API setting. |
vbFirstJan1 | 1 | Start with week in which January 1 occurs (default). |
vbFirstFourDays | 2 | Start with the first week that has at least four days in the new year. |
vbFirstFullWeek | 3 | Start with first full week of the year. |
Example-1:
Function FnDateDiff() Dim strDateOne Dim strDateTwo strDateOne = CDate("June 24, 2012") strDateTwo = CDate("June 25, 2013") strDayDiff = "Days difference is " & DateDiff("d", strDateOne, strDateTwo) strMonthDiff = "Months difference is " & DateDiff("m", strDateOne, strDateTwo) strYearDiff = "Years difference is " & DateDiff("yyyy", strDateOne, strDateTwo) strQuaterDiff = "Quaters difference is " & DateDiff("q", strDateOne, strDateTwo) strHoursDiff = "Hours difference is " & DateDiff("n", strDateOne, strDateTwo) strMintuesDiff = "Minutes difference is " & DateDiff("n", strDateOne, strDateTwo) strSecondsDiff = "Seconds difference is " & DateDiff("s", strDateOne, strDateTwo) MsgBox strDayDiff & vbCrLf & strMonthDiff & vbCrLf & strYearDiff & vbCrLf & strQuaterDiff & vbCrLf & strHoursDiff & vbCrLf & strMintuesDiff & vbCrLf & strSecondsDiff End Function
Example-2:
Function FnDateDiff2() Dim strDateOne Dim strDateTwo strDateOne = CDate("June 24, 2012") strDateTwo = CDate("June 25, 2013") MsgBox "Weeks Count starting from Sunday are " & DateDiff("w", strDateOne, strDateTwo, vbSunday) End Function
Also Read:
- VBA-Excel: Date-Time Functions – Month(), Year() and MonthName()
- VBA-Excel: Delete Blank Rows from Excel Work Sheet
- VBA Excel - Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation
- VBA-Excel: Arrays – Two Dimensional, Static Array
- VBA-Excel : 3D-Ranges – FillAcrossSheets Method