Macro to find the date difference in days/months/years in Excel

Example - Macro to find the date difference in days/months/years in Excel

There are many times in real life that you need to find the difference between 2 dates. For example - You want to calculate how many days have passed since you joined the company, you will have to use the date difference function in VBA macros.


Code and Syntax  - Macro to find the date difference in days/months/years in Excel

We can calculate the difference in terms of days, months or years as described below.

You can do this in two ways -

• By using Excel functions
• By using VBA/Macro functions

By using Excel functions -
Suppose you have date1 in A1 and date2 in A2. Cell A3 will have difference.

To find Days difference -
A3 Cell formula  "=A1-A2"     Days between the two dates
A3 Cell formula  "=NETWORKDAYS(A1,A2)" Weekdays between the two dates . This will exclude the saturday and sunday

To find Months difference -
A3 Cell formula  "=MONTH(A1)-MONTH(A2)" Months in the same year (3)
A3 Cell formula  "=(YEAR(A1)-YEAR(A2))*12+MONTH(A2)-MONTH(A1)" Months in different years

To find Year difference -
A3 Cell formula  ="YEAR(A1)-YEAR(A2)" Years occurring between two dates (3)


By using VBA macro functions -

VBA provides one function to find the difference between 2 dates as mentioned below.

You can use this function to determine how many specified time intervals exist between two dates. For example, you might use to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

Function calculatediff(myd)
   msgbox  "Diff between dates in days " & datediff("d", Now, myd)
End Function

In above example, we will be able to calculate the number of days between given date and today.