Learn how to calculate the difference between two dates in Excel. Maybe you have tried several times, but you are still getting errors. There are a few functions you can use to get the results faster. Find out how you can easily learn to format cells into Date or Number. This article also shows you how to calculate the age difference between two dates in Excel 2007 and more.
Table of contents
Calculate the difference between two dates in Excel
If you need to know the number of days between two dates, you can easily subtract one date from the other in Excel.
=End date-Start date
If the start date is greater than the end date, the result will be a minus number. The example below shows you how to write the subtraction of cell C2 in the formula bar. It also shows where to format the cell as general. Remember that the cell range (A2:B3) should be formatted into a Date and the cell range (C2:C3) into General or a Number. Otherwise, Excel will not show it correctly.
Please notice that writing a date can be different for each country. Some countries write the date in this order day/month/year or the following order month/day/year. Excel uses your computer’s date system. So if a cell’s date is not entered using the same date system, Excel will not see it as a true date. Even if you know how to calculate the difference between two dates in Excel, you can still get #VALUE as a result.
How to format cells into different date type
Let’s say that your computer’s date system is day/month/year, but you want to show the date in the order month/day/year. Then you can change this by following the steps below.
In the formula bar, you always have to write the date order as your computer’s date settings. Then you have to open the Format Cells box, which you can do in many ways.
- Select the cells you want to format > Right-click on one of the cells > Choose Format Cells, and the box will open immediately.
- Or select the cells and press CTRL + 1 on your keyboard. It will automatically open the box Format Cells.
The Format Cells opens automatically, and you are in the tab called Number. Under that, you can see that Category is highlighted. In the list underneath, you have to select Date. Then change the location into English (United States) to get the date order month/day/year. Then choose the type of date above it by scrolling down on the arrow. The date you select will be colored blue. Lastly, click OK.
The example below shows you the result of the dates. You can see that the dates are formatted in a different order, months/day/year. At the same time, the date in the formula bar will stay the same as your computer’s date system.
Calculate the age difference between two dates in Excel
First, you have to make sure that the cells that contain your dates are formatted to Date. To calculate the difference between two dates, you can use the DATEDIF function in Excel. The Excel DATEDIF function returns the difference between two dates in years, months, or days.
The DATEDIF function consists of two cells, followed by a year, month, or day.
=DATEDIF(start date,end date,unit)
Problem using DATEDIF function
Some people get an error when using the DATEDIF function. Start to write the formula in the cell you want and click on the start date, cell A2, followed by a comma.
The next step is to click on cell B2 (end date). Unfortunately, you can’t continue, and a box shows up, saying that there is a problem with the formula. Even though the instructions in Microsoft Office say that you can use a comma between the cells in the formula bar, you still get this problem.
You are trying to calculate the difference between two birth dates in Excel, but it is not working. Even if you follow the instructions as proposed, you might still get errors. That is because the DATEDIF function contains an error.
The solution to this problem is to use a semicolon (;) between the cells. So, use the semicolon, and the function should look like the example below.
Remember that the start date should be smaller than the end date, or you will get #NUM! as a result.
You have successfully learned how to calculate the difference between two dates in Excel. You had encountered some problems when using the DATEDIF function, but you can solve them now. Remember to change the cells that contain your dates are formatted to Date. Now you know how to calculate the difference between two dates in Excel 2007 and more.