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.
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.
- Select the target cell where you want to show the result.
- Next input the formula into the cell:
- And then press the button “Enter”. Thus, the result will show in the cell.
In this example, the dates are all listed in the worksheet. If not, you can input the dates directly into the formula:
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.
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.
Then double click the fill handle to fill the list.
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.
- Press the shortcut “Alt+F11” on the keyboard.
- And then right click the area in the “VBA Project”.
- Move your cursor on the option “Insert”.
- And then in the new menu, click the option “Module”.
- Next input the following codes into the module:
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))
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.
- Now press the “F5” to run the procedure. And then the result will also appear in the worksheet.
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.
|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.
|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.
|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.
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