Excel 2016 is one of the most complete office applications for the management and control of large amounts of data because thanks to its functions, formulas and simplification features makes this one of the best solutions..
One of the most delicate issues is in the work with dates, in some cases we can know the number of days, months or others between a range of dates and we are not clear about the process to achieve it, and it is logical, if we enter any wrong data the result, or gives a wrong data or simply generates an error.
Therefore, TechnoWikis through this tutorial will analyze how to subtract dates in this important spreadsheet.
Calculate days between two dates
1. How to calculate days between two Excel dates
Excel 2016 offers several alternatives to calculate the number of days between two dates, for this we have the following options, counting that we have data in columns A and B:
Subtract two dates
= B2-A2, with this formula we will subtract the two dates, B2 - A2, so that Excel 2016 recognizes this data as a date and will calculate the number of days between those two dates.
Subtract start date and end date
= DAYS (B3; A3), this function takes two arguments for the calculation that are the end date and the start date.
2. How to subtract months between two dates Excel 2016
To calculate the number of months between two dates in Excel 2016 it will be necessary to take into account the following terms:
EDate = previous date LDate = later date
Round up
If we want to round up, that is, this method does not make use of the day of the month in the calculations.
To do this we will execute the following syntax:
= (YEAR (LDate) -YEAR (EDate)) * 12 + MONTH (LDate)-MONTH (EDate)
In this case we will execute the following:
= (YEAR (B2) -YEAR (A2)) * 12 + MONTH (B2)-MONTH (A2)
Round down
On the contrary, if we want to round down the result, that is, this method makes use of numerical days in the calculations and rounds down to the nearest number of whole months, the syntax to be used is as follows:
= YES (DAY (LDate)> = DAY (EDate), 0, -1) + (YEAR (LDate) -YEAR (EDate)) * 12 + MONTH (LDate)-MONTH (EDate)
In this case we will execute the following:
= YES (DAY (B3)> = DAY (A3); H60; -1) + (YEAR (B3) -YEAR (A3)) * 12 + MONTH (B3)-MONTH (A3)
If you want to see the formulas used in each calculation, we go to the Formulas menu and there, in the Formula Audit group, select the option Show formulas:
3. How to calculate years between two dates Excel 2016
To calculate the number of years between two dates, just use the following syntax.
= YEAR (B2) -YEAR (A2)
We can see how simple it is to handle this type of data in Excel 2016..