Calculate Number of Days Between 2 Dates – Excel DateDiff

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]])

Parameters:

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

 yyyyYear
qQuarter
mMonth
yDay of the year
dDay
wWeek day
wwWeek of the year
hHour
nMinute
sSecond

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 :

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.

Examples:

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.