Welcome to Excel Avon
DATEDIFF Function in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
The Datediff function in VBA stands for “Date Difference between two dates.” the difference between two specified intervals. Here, the interval may be specified as hours/months/WEEK/days… etc. as specified by the user. This function takes three arguments. The first argument is what part of the difference we want, which can be years, days or months, or seconds and two dates, and the result is an integer.
To understand the function, look at the below syntax of the function.
DateAdd(Interval as String, Date1, Date2[fisrtdayofweek as vbsunday], [Fisrtweekofyear as vbFirstWeekofYear]=vbFirstJan1)
Arguments-
Interval –what way you want to calculate the date difference. The same list is below, whether in days, months, weeks, quarters, etc.
| Interval Type | Explanation/Description |
|---|---|
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of the year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minutes |
| s | Second |
Date 1: What is the first date you want to find the difference?
Date 2: What is the second date you want to find the difference from Date 1?
[First Day of Week]: It is optional. If this argument or parameter is omitted, it assumes Sunday (VbSunday) as the first day of the week.
[First Week of the Year]: It is optional. If this argument or parameter is omitted, it assumes January 1st (vbFirstJan1) as the first week of the year.
To understand for DateDiff Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
To understand DateDiff Function in Excel VBA, we will write a subroutine for DateDiff Function in Excel VBA after inserting the module in VBE.
Sub UseDateDiff() End Sub
After writing the subroutine we will define the variable
Sub UseDateDiff() Dim date1 as Date Dim Date2 as Date result as variant End Sub
Now I will Store first date on date1
Sub UseDateDiff() Dim date1 as Date Dim Date2 as Date Result as variant Date1 =#02-Jan-2020# End Sub
Now I will Store Second date on date2
Sub UseDateDiff() Dim date1 as Date Dim Date2 as Date Result as variant Date1 =#02-Jan-2020# Date2= #04-Apr-2022# End Sub
Now I will use DateDiff Function, to find the difference between years, we will use the ‘yyyy’ Interval.
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
result = DateDiff("yyyy', Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("yyyy", Date1, Date2)
debug.Print Result
End Sub
Click Run button,
Now you can see that we have got 2 years difference as result which is printed in the immediate window.
Quarter Difference
Now we will find the difference of quarter between ‘date1 to date2. Use Interval “q” for Quarter
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("q", Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("q", Date1, Date2)
End Sub
Click Run button,
Now you can see that we have got 9 Quarter difference as result which is printed in the immediate window.
Month Difference
Now we will find the difference of Month between ‘date1 to date2. Use Interval “m” for month
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("m", Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("m", Date1, Date2)
debug.print Result
End Sub
Click Run button,
Now you can see that we have got 27 Month difference between date1 to date2 as result which is printed in the immediate window.
Week difference
Now we will find the difference of week between ‘date1 to date2. Use Interval “ww” for week.
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("ww", Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020#
Date2= #04-Apr-2022#
Result = DateDiff("ww", Date1, Date2)
debug.print Result
End Sub
Click Run button,
We get the difference between date1 to date2 is 118 weeks. which is printed in the immediate window.
Minutes difference
Now we will store the time in date1 and date2 and find the difference of time in minutes then we will store the time first.
Sub UseDateDiff() Dim date1 as Date Dim Date2 as Date Result as variant Date1 =#02-Jan-2020 1:34:40 AM# Date2= #04-Apr-2022 3:40:20 PM# End Sub
Now we will use Datediff function and write ‘n’ for minute interval.
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020 1:34:40 AM#
Date2= #04-Apr-2022 3:40:20 PM#
Result = DateDiff("n", Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020 1:34:40 AM#
Date2= #04-Apr-2022 3:40:20 PM#
Result = DateDiff("n", Date1, Date2)
debug.print Result
End Sub
Click Run button,
We found the difference in minutes between date1 to date2 which is 1185966 which is in the image below
Hour’s difference
Find the difference between date 1 and date 2 in hours, we use ‘h’ interval for hours.
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020 1:34:40 AM#
Date2= #04-Apr-2022 3:40:20 PM#
Result = DateDiff("h", Date1, Date2)
End Sub
Use debug. Print for printing result
Sub UseDateDiff()
Dim date1 as Date
Dim Date2 as Date
Result as variant
Date1 =#02-Jan-2020 1:34:40 AM#
Date2= #04-Apr-2022 3:40:20 PM#
Result = DateDiff("h", Date1, Date2)
debug.print Result
End Sub
Click Run button,
We found the difference in Hours between date1 to date2 which is 19766hours which is in the image below
So, I hope you have understood How to use DateDiff Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about DateDiff Function in Excel VBA
