Knowing how much time has passed between two specific years is as simple as subtracting their numbers, however, in work environments the demands are often more complex. So, It is not uncommon to find yourself needing to subtract whole dates in an Excel document.. This program is a suite aimed at carrying out all kinds of mathematical operations and the subtraction of two dates is no exception. In that sense, we are going to show you everything you need to know to do it and apply it in your own spreadsheets.
If you need to know how many days, months or years have elapsed between two dates, you have come to the right place because we are going to give you all the information to achieve it in a matter of seconds..
How to subtract dates in Excel?
Subtracting dates is a task that in Excel offers several ways to achieve a result, however, which one to take will depend on how we want to display it.. In this sense, the program is capable of indicating the days that have elapsed, the months and also the years, although separately. If you want to see the complete information, then you will have to occupy 3 cells to generate each result and later, occupy a formula to concatenate the string of characters. However, Excel is a program where the possibilities are open and there are ways to build formulas that allow us to show the result of the subtraction of dates in the format we want.
Here we will see the simplest and most accessible ways to achieve it.
Basic date subtraction
This is the easiest method to subtract dates in Excel and it is simply about doing a subtraction, selecting the cells where the dates in question are located. In this way, all you will have to do is the following:
- Double click on the cell where you want the result.
- Enter the equals sign ( = ).
- Select the date number 1.
- Inserts the minus sign (-).
- Select the date number 2.
- Hit Enter.
This will return the number of days that have elapsed between both dates.. It should be noted that date number 1 must be the most recent, in order to obtain a positive figure.
Subtracting dates with the DATEDIF function
If you are looking for the function IF DATED in the Excel catalog, you probably won't find it, however, it is still available and functional. Its job is precisely to show the difference between two dates, with the possibility of defining if we want it to be expressed in days, years or months. In this way, if you want to have the complete information about the elapsed time, it will be enough to occupy 3 cells in a row, one for each measure of time.
The syntax of this formula is:
=DATEDIF(Date1,Date2)
However, unlike the previous case, in the DATEDIF function we need date number 1 to be the oldest. If we do it the other way around, the program will throw the error #¡NUM!
To carry out the subtraction of dates and that the result is shown in days, months and years you must do the following:
- Double click on a cell.
- Enter the function IF DATED and open parentheses.
- Click on date number 1 (the oldest) and enter a comma.
- Click on date number 2 (the most recent) and enter a comma.
- Enter in quotation marks the letters d, y and m that represent days, years and months respectively.
- Close the parenthesis.
- Hit Enter.
According to the type of letter that you have inserted at the end between the quotes, the result will be displayed in days, months or years. Thus, you can easily subtract dates in Excel and know the amount of time that has elapsed between two specific points on the calendar.
Conclusion
Subtracting dates in Excel is an important procedure to know for anyone who works with data and needs to perform time calculations.. Although it may seem a bit complicated at first, it is quite easy to use once you understand the basics. Take into account that each of the ways that we explain has its particularities and understanding them will depend on obtaining the correct result.
It's important to remember that Excel uses January 1, 1900 as its base date, so it's crucial to keep this in mind when entering dates. In addition, it is necessary to know how to format the cells so that the results are displayed as we want.
As we have seen, there are different ways to subtract dates in Excel, from using the DATEDIF function to manual calculation using simple formulas. Each method has its advantages and disadvantages, so it is valuable to choose the option that best suits your needs. Remember, also, that you have the possibility of practicing during the course of your work with each procedure in order to become familiar with any and that this is your main way of subtracting dates in Excel.