|
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 : Strings Functions – Ucase
- VBA-Excel: String Functions – RTrim()
- VBA-Excel: Array Functions – Filter()
- VBA-Excel: Date-Time Functions – DateSerial()
- VBA-Excel: String Functions – Replace()