Manually counting the days between dates is undoubtedly tricky. However, exercising smaller ranges is not a worrisome task as compared to multiple dates. Fortunately, with a date calculator, you can find a feasible answer to how to subtract dates in Excel.
Having an Excel sheet with multiple dates will end up calculating the differences between messy dates. No matter, what things made you do it, ultimately you will need to find a better way of subtracting dates in excel. The way you calculate the values in Excel can become trickier or easier. It simply depends on which method you choose for this function.
Basically, Excel has multiple date-based functions as well as formulas that help subtract dates. For instance, if you need to figure out the number of months between 2-5-2016 and 1-15-2016, what should be the answer to it? Should it be 0 or 1?
Some people may agree with the answer “0” and some may go with the value “1”.
Well, you need to ensure formatting cells before start executing beginning. For macOS, you need to run command ⌘+1 and for Windows, you have to press CTRL+1. For all kinds of subtractions, you have to format cells to the date and number as per need.
In case of exercising subtraction of two dates, you may use the DATEIF function.
In the beginning, you have to enter the start date in a cell, and in another cell put the end date. Now, enter the formula as needed.
Remember that you will get the #NUM! the result if your End_date is smaller than the Start_date.
How to Subtract Dates in Excel?
Let’s figure out different ways to subtract dates in Excel:
Find Difference in Days
Here in cell D9, you can witness the start date and in cell E9 the end date is given. In cell F9, you will have to put the formula. The ending result will be the “d” returns in terms of the number of total days between the two dates.
Find Difference in Weeks
In the example given above, cell D13 has the start date and cell E13 has the end date. Likewise, you will find the “d” returns in terms of the number of days. On the other hand, you will also find the /7 value given at the end. As you know there are 7 days a week that’s why it is divided by the number of days. Remember that the outcome should be formatted as a number. For this, you have to press CTRL+1 then hit Number > Decimal places: 2.
Find Difference in Months
Here in this example, you will see the end date given in cell E5 and the start date is given in cell D5. Implementing the formula results in the “m” that returns the number of full months given between the two days.
Find Difference in Years
When it comes to figuring out the difference in years, the example shows the start date in cell D2 and the end date in E2. Resulting the “y” returns in terms of the number of total years between the two days.
Let’s have a deeper insight into subtraction dates in Excel.
Calculate Days Between Two Dates
Figuring out the number of days between two dates is counted as the trouble-free calculation as compared to others. To calculate the value, just one method is available to practice. That’s why you don’t have to worry about remembering different formulas.
In the example given above, two different dates are shown in A2 and A3 cells. The difference between these two values is 802 days, which is also given at the right. For calculating the same value, Excel always provides multiple methods as per your needs and interests.
In the first formula, you can see an easy subtraction function, which is of two dates, A3 – A2. You don’t need to tell Excel about dates as it is already mature enough to understand the values. That’s why it simply subtracts the number of days between two dates. Furthermore, you can use the function DAYS.
=DAYS(A3, A2)
In this function, you will see two arguments working, the start date and the end date. The resulting value will be a negative number in case you switch the dates in the formula. As mentioned earlier, DATEDIF is another feasible way to practice.
=DATEDIF (A2, A3, “D”)
While typing the formula, you will notice that Excel will not suggest anything for the fields. That’s why you have to be prepared for it beforehand.
Calculate Months Between Two Dates
Well, you may count it as one of the touchy calculations because of the number of months. It always depends on how you count the month whether it is a whole month or a partial month. Talking about calculating months between two dates, Excel has a MONTHS function in Excel. However, it is not extensive because it allows calculating the month only and not the year.
It simply shows that the function is useful for calculating the difference between two months of the same year. Alternatively, you can exercise the formulas given below for the functioning of a situation other than two months in the same year.
Round Up – =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
Round Down – =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)
Well, this is not an easy formula to remember, that’s why Excel has fed them in.
Keep in mind that editing the round-down formula in the real cell is more important because the whole formula will not appear in the cell itself. Click the Formulas tab and press Show Formulas.
Did you find the final value of the given formulas on the currently debating example?
Let’s have a look:
27 months is the final value resulting from the round-up and 26 months comes as a result of round down. Well, it is an accurate value based on how you look at it.
This is how to subtract dates in Excel. Keep practicing and improve the skills with brilliance.
Things to Consider When Subtracting Date in Excel
- DATEDIF function is not an apparent formula used in excel. You have to find it.
- Selection of the latest date at first is mandatory when you are practicing direct subtraction. In the next step, you have to select the older ones. Ignoring this rule will end up giving you a value in minus.
