Microsoft Excel , currently in its 2019 edition, is one of the most versatile applications developed by Microsoft through which it is possible to manage various types of data both numerical and dates , text and others, to display accurate and true results that can be represented in various formats or elements such as pivot tables..
The management and use of data should be a precise task since the slightest error can trigger a series of problems that will prevent the results from being the most appropriate. Dates are one of these critical issues since working with days, months or years can lend itself to errors that, although minimal, will affect all other results associated with them.
TechnoWikis will explain in detail how to work with dates in Microsoft Excel 2019 and thus manage all date values ​​in this powerful application..
1. How to calculate days between two dates Excel 2019
When using Microsoft Excel 2019 we will be able to calculate the number of days between two specific dates, this allows us to have a better control of this type of information since in some cases, especially audit or human resources, knowing this information is vital. There we have two options that are:
Subtract two dates
For this case we have the dates in cells A2 and B2, the formula to be applied will be “= B2-A2â€, when using this formula the two dates, B2 - A2 will be subtracted, so Excel 2019 recognizes this data as a date and will the respective calculation of the number of days between those two selected dates.
= B2-A2
Subtract start date and end date
For this method we must use the function
= DAYS which selects two arguments for the calculation which are the end date and the start date, the syntax is:
= DAYS (B3; A3)
2. How to subtract months between two dates Excel 2019
Step 1
Microsoft Excel 2019 allows us to calculate the number of months between two specific dates, for this we must take into account the following terms to use:
Step 2
When using these functions we have some tasks such as:
Round up
When using this method, the day of the month will not be used in the calculations and the syntax to be used will be as follows:
= (YEAR (LDate) -YEAR (EDate)) * 12 + MONTH (LDate)-MONTH (EDate)
Round down
When implementing this method, Excel 2019 will use numerical days in the calculations and round down to the nearest number of whole months, the syntax to be used is the following with this method:
= YES (DAY (LDate)> = DAY (EDate), 0, -1) + (YEAR (LDate) -YEAR (EDate)) * 12 + MONTH (LDate)-MONTH (EDate)
Step 3
If we want to know the formulas used in each calculation, we must go to the "Formulas" menu and in the "Formula audit" group, select the option "Show formulas":
Step 4
When executing this, the implemented formulas will be reflected in the cells where the results are:
3. How to calculate years between two dates Excel 2019
Another of the options offered by Excel 2019 is the calculation between two dates to obtain the years, the formula to be used will be the following:
= YEAR (B2) -YEAR (A2)
4. How to add days to a date in Excel 2019
It is possible to add or decrease days to a specific date in Excel 2019, this will result in the exact date according to the days we add or subtract.
Step 1
The syntax to use is as follows:
= date + days
Step 2
In this case we have the following data:
- In cell A2 we will enter the date to be edited.
- In cell B2 we will enter the days to add which can be positive or negative.
- In cell C2 we will see the result of the formula.
Step 3
The formula to be used in cell C2 will be the following:
= A2 + B2
Step 4
This formula can be dragged to the other cells to apply the respective formula and obtain the addition or subtraction of days as appropriate.
5. How to add years to a date in Excel 2019
Step 1
It will also be possible to add years to a date defined in Microsoft Excel 2019, for this case the syntax to be used will be:
= DATE (YEAR (date) + years; MONTH (date); DAY (date))
Step 2
In this case we will use the following formula. By dragging the formula it will be applied to the other cells and we will see that the years indicated in column B are added to the initial date.
= DATE (YEAR (A2) + B2; MONTH (A2); DAY (A2))
In this way we have these options to work with dates in Microsoft Excel 2019 without errors.