2 Methods to Get the Difference Between Two Dates in Excel

Data Recovery, Excel File Recovery, Excel Solutions, File Recovery, Office Solutions, Solutions October 24, 2016

Excel will certainly do a lot of work relating to dates. In this article, we will introduce 2 ways to get the difference of 2 given dates.

To calculate the time or date difference in Excel is a very common task in Excel. For example, you may need to calculate how many days that employees have worked in the company.An Example for DATEDIF Function

In this image, there are the dates of different employee that working in the company. And now you need to calculate how long that they have been in this company. The following will talk about 2 ways to finish this task in detail.

Method 1: Use Excel Built-in Function DATEDIF

Using function in Excel can help you solve problems quickly. And to calculate the numbers, you can apply the DATEDIF function.

  1. Select the target cell where you want to show the result.
  2. Next input the formula into the cell:

=DATEDIF(B2,C2,”D”)

  1. And then press the button “Enter”. Thus, the result will show in the cell.Input the Formula

In this example, the dates are all listed in the worksheet. If not, you can input the dates directly into the formula:

=DATEDIF(“6/5/2015″,”10/19/2016″,”D”)

Remember if you input the date into the formula, you need to add the quotation marks to the date. Otherwise the formula cannot recognize the dates correctly. To fill the whole list, double click the fill handle of the cell. Thus, the days of other employees will also be calculated.

Other Days

Here we will calculate the days between two dates. And if you want to calculate the months or years difference of two dates, you can also change the unit into “M” or “Y in this formula. And then input the formula into the corresponding cell.

=DATEDIF(B2,C2,”M”)

Then double click the fill handle to fill the list.Calculate Months

Hence, in your actual worksheet, if you need to calculate the days or months between two given dates, you can use this formula as you like.

Method 2: Use VBA Function DATEDIFF

Instead of using the DATEDIF function, here you can also use VBA function DATEDIFF to do the same task.

  1. Press the shortcut “Alt+F11” on the keyboard.
  2. And then right click the area in the “VBA Project”.
  3. Move your cursor on the option “Insert”.
  4. And then in the new menu, click the option “Module”.Insert a Module
  5. Next input the following codes into the module:

Sub TimePeriod()

Dim nRowId As Integer

For nRowId = 2 To 10

    Worksheets(“Sheet1”).Cells(nRowId, 4) = DateDiff(“d”, _

    Worksheets(“Sheet1”).Cells(nRowId, 2), Worksheets(“Sheet1”).Cells(nRowId, 3))

Next nRowId

End Sub

Here in this example, there are only 9 rows of dates. And in your actual worksheet, you can manually modify the number according to your need. Besides, if you need to calculate the months, you may change the “d” into “m” in the arguments of the “DATEDIFF” function.

  1. Now press the “F5” to run the procedure. And then the result will also appear in the worksheet.The Result of vba
    The ResulT of vba

You may also compare with the former method. The results are all the same.

The comparison between the Two Methods

Of course these two methods have different emphasis. The table below shows the advantages and disadvantages of the above two functions.

Method Comparison

DATEDIF function

DATEDIFF function

Advantages

 Easy to use since it is built-in and its usage is simple, no complex parameters. 1.       Support 10 types of intervals.

2.       With two additional parameters, you can control more, such as the first day of the week, first week of the day, etc.

Disadvantages

 1.       Only 6 types of intervals are supported.

2.       Cannot control many values, such as the first day of the week, etc.

 1.       You must know VBA programming

2.       It will increase the complexity if you only need to get the difference in days, months or years.

3.       More parameters which may confuse the user.

Conclusion

 Due to its simplicity, this function can be used for most of cases if the interval unit is day, month or year. You need this function only when you want to get more controls.

Therefore, you can choose either of the two methods according to your need.

Sometimes you will meet with such a situation that you cannot use Excel. The features are all disable. Hence you must consider that you meet with Excel corruption. Therefore, to fix Excel, you can use our effective recovery tool. It can help you deal with Excel problems and you can soon use Excel again.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.