Microsoft Excel is the office automation program par excellence to carry out all kinds of procedures, reports and operations. Unraveling all its secrets is a task that requires time and dedication on the part of most students and workers who must regularly deal with the tool.
We do not want to cover so much in this article. We simply bring you a small guide with several examples that will show you how to effortlessly add or subtract different dates from the latest versions of Excel. Here's how to manage all date values in this great app.
Before we start, we have to know that Excel gives a number to every day since 01/01/1900. This number is known as the "serial number . " So that you can see what we have just explained, observe in the image the date entered in cells A1 and B1. It really is the same, only that in cell B1 the "Serial Number" corresponding to that same date is reflected.
To find this nomenclature, we have simply selected cell B1 and then, within the "Number" group, select the "General" format. This format will allow us to perform calculations of all kinds.
Subtract dates in Excel
There are several alternatives to calculate the difference days between two dates :
- Subtract days: by using the formula "= A2-B2" we can easily find the number of days between one date and another.
- Subtract days with start date and end date: with the function “= DAYS (B3; A3)”, a start date and an end date are taken as reference.
- Subtract months: to calculate the number of months between two dates it will be necessary to take into account the following formula “= (YEAR (B4) -YEAR (A4)) * 12 + MONTH (B4) -MONTH (A4)”.
- Subtract years: to calculate the number of years between two dates, simply use the following syntax "= YEAR (B2) -YEAR (A2)".
- Subtract with the DATE function: in the following formula we will use the DATE function to obtain the difference in days between both dates. In this example, the start date is in cell A4 and the end date is in B4, where "d" gives us the data with the number of full days between both dates.
In the latter case, it is important that we look at the location of the start date and the end date . The first must have a lower value than the second, otherwise the result will be #NUM !. It is also important to bear in mind that, if we change the “d” at the end of the formula, for “m” or “y”, we will be able to obtain the difference of months and years respectively.
Sum dates in Excel
In the same way as in the previous case, we have several options to add or remove days, months or years to a specific date:
- Add days: by using the formula "= A2 + B2 , only this time we add a" + "symbol between the value of both cells.
- Add days, years and months: it will also be possible to add the time interval that we want to a specific date . In this case the formula to use is the following:
- "= DATE (YEAR (A3) + B3; MONTH (A3); DAY (A3))" in case we want to add years.
- "= DATE (YEAR (A4); MONTH (A4)) + B4; DAY (A4))" in case we want to add months.
- "= DATE (YEAR (A5); MONTH (A5)); DAY (A5) + B5)" if we want to add days.
As we have verified, we can know the number of days, months or years within a range between two dates. From here on, it's up to us. Remember that in Excel you have to pay attention to the values that we enter in the formulas, because if we enter any data wrong, the result will generate an error .