You will often find yourself needing to work with dates in Microsoft Excel.
When starting out, you may find that date and time calculations can be a bit tricky to understand.
Since Excel stores dates as serial numbers.
However, there are functions that you can use, to work with dates and times that will make your life easier. We are going to look at the three main ways to calculate the difference between two dates, in Microsoft Excel.
So, let’s get started.
Calculate the Difference Between Two Dates Using Simple Subtraction
You can simply subtract one date from another one in order to see the difference in days.
We have a start date and an end date, given.
The start date should always be the earlier date, while the end date should be the later date when working with subtraction.
You can calculate the difference between the two dates, using the subtraction arithmetic operator.
- So, in cell C2, enter the following formula and press Enter.
=B2-A2
- You should see the following.
This gives the difference between the two dates in days.
Calculate the Difference Between Two Dates Using the DAYS Function
The DAYS Function is used to return the difference in days, between two dates.
The syntax of the DAYS Function is:
=DAYS(end_date, start_date)
- end_date – this is the later date. This is a required value.
- start_date – this is the earlier date. This is a required value.
So, let’s see how to use the DAYS Function.
We have a start date and an end date, given in our example below.
- In cell C2, enter the following formula and press Enter.
=DAYS(B2, A2)
2) You should see the following.
This gives the difference between the two dates in days.
Also read: Get the Day Name from the Date in Excel
Calculate the Difference Between Two Dates Using the DATEDIF Function
We can calculate the difference between two dates, using the DATEDIF Function. The DATEDIF Function comes from the Lotus 1-2-3 package and was incorporated for the purposes of compatibility in Excel.
The DATEDIF Function is used to return the difference between two dates in either days, months or years depending on the unit you specify.
Excel won’t show the syntax, as one types this function, as is the case for other functions.
The syntax of the DATEDIF Function is:
=DATEDIF(start_date, end_date, unit)
- start_date – this is the first date that you specify, the earlier date in other words. You can use cell references with this function. However, you can enter the date directly into the function as a text string, using quotation marks. Alternatively, you can enter the date as a serial number, or as the result of another function such as the DATE Function. This is a required value.
- end_date – this is the last date that you specify, the later date in other words. This is a required value.
- unit – this is the time period that you’d like to be used in the calculation and returned. This can be days, months or years for example. This is a required value. The table below shows what the unit value can be.
Unit (third argument in DATEDIF Function) | Definition |
---|---|
d | the difference between the two dates is returned in days |
m | the difference between the two dates is returned in complete months |
y | the difference between the two dates is returned in complete years |
md | the difference between the two dates is returned in days, and the months and years are ignored |
ym | the difference between the two dates is returned in months, and the days and years are ignored |
yd | the difference between the two dates is returned in days and years are ignored |
Calculate the Difference Between Two Dates in Days
Let’s look at our simple example again, we have the two dates given below.
We want to use the DATEFDIF Function to calculate the difference in days, between the two dates given.
- In cell C2, enter the following formula and press Enter.
=DATEDIF(A2,B2,"d")
- You should see the following.
This gives the difference between the two dates in days.
Calculate the Difference Between Two Dates in Months
We have two dates given below. We want to use the DATEFDIF Function, to calculate the difference in months, between the two dates given.
- In cell C2, enter the following formula and press Enter.
=DATEDIF(A2,B2,"m")
- You should see the following.
This gives the difference between the two dates in months.
Calculate the Difference Between Two Dates in Years
We have our two dates given below. We want to use the DATEFDIF Function to calculate the difference in years, between the two dates given.
- In cell C2, enter the following formula and press Enter.
=DATEDIF(A2,B2,"y")
- You should see the following.
This gives the difference between the two dates in years.
Note: If you enter the later date first, you will get a #NUM! error. So just be sure to double-check that you entered the earlier date first, if you get this error.
Calculate the Difference Between Two Dates Using DATEDIF and DATE Function
You can enter the date directly into the DATEDIF Function, by using the DATE Function in a formula.
Just a quick reminder, the DATE Function is used to assemble a valid date from given inputs for the year, the month, and the day.
It’s safest to use the DATE Function when you want to input dates directly into a formula. Since not all date functions can compute dates that have been entered directly as text.
The syntax for the DATE Function is:
=DATE(year, month, day)
- year – this is a number representing the year. This is a required value.
- month – this is a number representing one of the months of the year. It can be a value between 1 and 12. This is a required value.
- day – this is a number representing one of the days of the month, at hand. So, it can be a value between 1 and 31. This is a required value.
So, let’s say you have a situation where you want to calculate the difference between two dates.
The earlier date is 5 March 2022, and the later date is 7 August 2022. You’d like to input these dates directly and have the difference returned in days.
So, let’s see how you can accomplish this.
- In cell A2, enter the following formula and press Enter.
=DATEDIF(DATE(2022,3,5),DATE(2022,8,7),"d")
- You should see the following.
This gives the difference between the two dates in days.
Conclusion
While date and time calculations can be confusing at first, as you practice you will gain more clarity.
Consequently, you will soon understand how to manipulate dates and times in Excel.
In this tutorial, we have reviewed the main ways to calculate the difference between two dates in Excel, which you can utilize when needed.
Other articles you may also like: