Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Date-Time Functions – DateDiff()

Description:

The Date­D­iff() func­tion returns the spec­i­fied time inter­val dif­fer­ence between the two dates provided.

Format:

DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])

Arguments:
  • inter­val
    • Manda­tory
    • Type: String expression
    • The time inter­val you want the dif­fer­ence between two dates.

Set­ting Descrip­tion
Yyyy Year
Q Quar­ter
M Month
Y Day of year
D Day
W Week­day
Ww Week
H Hour
N Minute
S Sec­ond

 

  • Date1
    • Manda­tory
    • Type: Date
    • Date1, one of the dates for which dif­fer­ence is to be calculated
  • Date2
    • Manda­tory
    • Type: Date
    • Date2, one of the dates for which dif­fer­ence is to be calculated
  • First­day­ofweek
    • Optional
    • Type: Numeric, Text
    • Spec­i­fied the first day of the week, default : Sunday
Con­stant Value Descrip­tion
vbUs­eSys­tem 0 Use the NLS API setting.
vbSun­day 1 Sun­day (default)
vbMon­day 2 Mon­day
vbTues­day 3 Tues­day
vbWednes­day 4 Wednes­day
vbThurs­day 5 Thurs­day
vbFri­day 6 Fri­day
vbSat­ur­day 7 Sat­ur­day
  • First­weeko­fyear
    • Optional
    • Type: Numeric, Text
    • Spec­i­fied the first week of the week.
Con­stant Value Descrip­tion
vbUs­eSys­tem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which Jan­u­ary 1 occurs (default).
vbFirst­Four­Days 2 Start with the first week that has at least four days in the new year.
vbFirst­Full­Week 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

 

DateDiff() -1

Date­D­iff() –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

 

You may also like...

1 Response

  1. temp2050 says:

    https://www.youtube.com/watch?v=ynBzBxjn06M

    this can get the diff between two dates in years and mon­thes and dayes

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: