How To Find Number of days between Two Dates?

Sometimes we need to know the exact time gap or days in between two consecutive years or months.

Here you will find how to find this difference in:

  • Worksheet
  • Excel VBA Macro code

1. Worksheet Find Days between 2 Dates

Simplest method is to use plain subtraction on 2 date fields . Assume you have date in cells A1 & A2 as given below.

A1: “2-Jan-2018”

A2: “1-Jan-2018”

To get the difference between these two dates in cells A3, Type formula

A3: “=A2-A1”

This will give the value 1. i.e., Number of days between these 2 dates is: 1 day.

2. Excel VBA DateDiff Function

You can find them out using the normal & easy code work given here.

Syntax: Vba.DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])


The interval of time that is used to calculate the difference between date1 and date2. Valid  interval values follows:

 yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Week day
ww Week of the year
h Hour
n Minute
s Second

Excel VBA Code – Calculate Number of Days between 2 Dates

Like wish u can use any parameter along with the syntax to know the difference between two dates . And it’s super easy and simple now.

Place a command button on your worksheet & add the given lines :

Sub Find_Days_Between_Dates()
    Dim firstDate As Date, secondDate As Date, n   As Integer
    'Two Date Values
    firstDate = DateValue("Jan-1-2018")
    secondDate = DateValue("Jan-12-2018")
    'Find number of Days
    n = DateDiff("d", firstDate, secondDate)
    MsgBox n

    'Find Weeks
    n = DateDiff("ww", firstDate, secondDate)
    MsgBox n
End Sub

We decide two dates. Next, we initialize the two dates using the DateValue function. The DateDiff function has three arguments.

  1. Fill in “d” for the first argument
  2. We want the number of days between two dates.
  3. We use a MsgBox to display the number of days between the two dates.

Result appears in a msg box when you click the command button on the sheet.


DateDiff(“yyyy”, “12/11/2000 “,”12/11/2013”)

Result: 13

DateDiff(“m”, “1/04/2011”, “1/08/2011”)

Result: 4


Note: We can use  “d” to “ww” to get the number of weeks between two dates. Place your cursor on DateDiff in the Visual Basic Editor and click F1 for help on the other interval specifiers.

External Reference: MSDN Excel VBA DateDiff Function

They can be used in:

Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel 2000.

Leave a Reply