Be the first user to complete this post

  • 0
 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
    • Manda­tory
    • Type: String expression
    • The time interval you want the difference between two dates.
SettingDescription
YyyyYear
QQuarter
MMonth
YDay of year
DDay
WWeekday
WwWeek
HHour
NMinute
SSecond
  • 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
ConstantValueDescription
vbUseSystem0Use the NLS API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday
  • Firstweekofyear
    • Optional
    • Type: Numeric, Text
    • Specified the first week of the week.
ConstantValueDescription
vbUseSystem0Use the NLS API setting.
vbFirstJan11Start with week in which January 1 occurs (default).
vbFirstFourDays2Start with the first week that has at least four days in the new year.
vbFirstFullWeek3Start 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
DateDiff() -1
DateDiff() -1

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
DateDiff()-2
DateDiff()-2

Also Read:

  1. VBA-Excel: String Functions – strComp()
  2. VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)
  3. Excel-VBA : Range - Lock/Unlock Specific Cells
  4. VBA-Excel: Arrays – One Dimension, Dynamic Array
  5. VBA-Excel: String Functions – Trim()