Excel Difference Between Two Dates
If we work on many projects, we may need to know how many months have passed between the two dates. Similarly, if we are in the planning phase of a project, we may need to know the start and end dates. The number of months between two dates can be calculated in various ways (all using different formulas).
We’ll look at different formulas for calculating the difference between two dates in this tutorial.
Calculate Difference Between Two Date Using the Function ‘DATE-DIF’
The most appropriate function to determine the difference between the two dates is ‘DATE-DIF’. In Excel, if we want to compute the difference between two dates in years, months, or days, then the DATEDIF function will manage it.
It is a unique function because it is not included in Excel’s Date & Time function. So, if we go to Formulas, we will find over 450 Excel functions, and if we go to Date & Time, we will not see DATEDIF.
1. Calculate the Difference Between Two Given Dates in Days
If we want to know how many days there are between two dates, we can use the following formula:
1. First, we have to enter the below date function in the blank cell in which we need to get the result.
Note: A2 denotes the start date cell, while B2 denotes the end date cell in the formula above.
2. Next, to fill this formula, we have to drag the fill handle down to the cell, and the number of days between the dates is determined as shown in the screenshot below:
2. Calculate the Difference Between Two Given Dates in Months
If we wish to find out how many months there are between two dates, we can use the formula below in a blank cell:
Note: The start date cell is A2, while the end date cell is B2.
Then, we have to drag the fill handle down to the cells so that we can apply the above formula, and the difference between two dates in months has been determined, as shown in the screenshot below:
3. Calculate the Difference Between Two Given Dates in Years
We can also use the DATEDIF function in order to calculate the number of years between two dates:
To determine the difference between two given dates in years, we have to type the following formula into the blank cell:
Note: The start date cell is A2, whereas the end date cell is B2.
Then, to use this formula, drag the fill handle down to the cells, and the number of years between the specific two dates is returned, as shown in the below screenshot:
4. Calculate the Difference Between Two Given Dates in Days, Months, and Years
If we want to calculate the difference between two dates in years, months, and days together, then we have to use the following formula:
Note: A2 is the start date cell, and B2 is the end date cell.
After that, we have to drag the fill handle down to the cells which we want to apply the above formula; then we will see the following output:
Note that we can also use double-quotes to manually specify dates in the DATEIF function. Simply double-check that the dates in double-quotes in Excel are in a valid date format.
5. Calculate the Difference Between Two Given Date Times in Days, Hours, and Minutes
The following formula can help us compute the difference between two date times in days, hours, and minutes if we have two lists of dates and times.
We need to type the following formula into a blank cell:
NOTE: A2 is the start date-time cell, and B2 is the end date time cell:
After that, drag the fill handle down to the cells where we want to apply the formula, and we will get the desired result:
Count Days Between Dates with Excel DAYS Function
MS Excel 2013 introduces the DAYS function. Its purpose is to figure out how many days there are between two dates. In Excel, the DAYS function is a Date/Time function via which we can calculate the number of days between two dates.
Please note that, unlike DATEDIF, the arguments for a DAYS formula must be entered in the reverse order.
So, our formula takes the following shape:
=DAYS(B2, A2)
Depending on whether the end date is smaller or greater than the start date, it returns the difference as a positive or negative, similar to subtraction:
How to Calculate Working Days between Two Dates in Excel
If we want to find out how many working days are between two dates, then we can use the NETWORKDAYS function:
The first two arguments are well-known, while the third argument which is optional, permits us to omit a custom list of holidays from the day count.
Use the following formula to calculate the number of working days between two dates in column A and column B.
=NETWORKDAYS(A2, B2)
We can also enter our holiday list in few cells and tell the formula to leave out those days:
=NETWORKDAYS(A2, B2, $A$9:$A$10)
As a result, only business days are counted between two dates:
Note: If we have to manage custom weekends such as Saturday or Sunday or Sunday only, we have to use NETWORKDAYS.INTL function which permits us to define, which days of the week must be considered weekends.
