Calculate Excel Date Difference in Days – Get Simple Formula

How To Calculate Excel Date Difference?

To get the difference between 2 days & get the number of days between these dates, you can use one of these methods.

Here you will find how to find Excel Date difference in days:

• Worksheet – Formula to calcualte Date difference
• Excel VBA Macro code – Programatically find differences

Make sure that the input dates are in same format or notation. And also, it is not stored a string in the Excel worksheet. If so, then you can to cleanp the format and then perform this date difference calculations.

1. Excel Formula to calculate Days between 2 Dates

To find the difference between two dates in Excel, the simplest method is to use plain subtraction on 2 date fields. How do we do this?

Assume you have date in cells A1 & A2 as given below.

• A1: “2-Jan-2018”
• A2: “1-Jan-2018”

To calcualte & 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. Note that the cell in which you are displaying the results,  (in this case it is cell A3) should be formatted to number and not date.

If it is formatted to display date, then you will not get a value as 1, but instead Excel will display this number a date.

Lets see what are the other parameters available in this function and what other type of results we can get a output.

2. VBA Excel Date Difference Calculation

There is a build in function available in VBA, to calculate the Excel date difference between 2 days. The function is ‘DateDiff’

Syntax of this function is: DateDiff (interval, date1, date2 [, firstdayofweek [,firstweekofyear]] )

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

Once we specify the interval, we will have to input the 2 dates that we would like to calculate the difference.

As you can see that in our example we are going to use the parameter “d” – which gives the Excel date difference in days for the input values.

Lets see how to use this in actual vba code.

2.1. 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.

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.

Most Popular Topics in OfficeTricks:

1. Excel VBA Get Username – 6 Easy Ways using Macro
2. VCF to Excel Converter – VBA Code to Read vCard File
3. Excel To VCF Converter – 81,000+ Downloaded – Excel To Vcard App