How to add DAYS to a date in Excel
Excel stores date value in integer number, to find out n days after a date, add the date and number of days.
The Result
The formula in cell B2 adds a date in cell A2 with number 20. The result, 20 days after May 16, 2019, is June 5, 2019.
There are many ways to get a date value, for the first formula the date value is inserted in a cell, the second formula uses the DATE function then added to the number 20. The result is the same as the first formula.
The third formula uses the TODAY function, this function returning a different date depending on what date it is today. Incidentally, today is May 16, 2019 add number 20 the result is the same as the two previous formulas.
Read the article below for a more detailed explanation of how Excel stores date and time value.
How to add WEEKS to a date in Excel
To find out n weeks after a date, use the addition and multiplication formula. The multiplication formula used to convert weeks into days by multiplying by 7. The addition formula used to add the results of the multiplication formula with a date. The result is n weeks after a date.
The result
1 week after May 16, 2019, is May 23, 2019, and 4 weeks after May 16, 2019, is June 13, 2019.
You can use negative numbers to find n weeks before a date. The result of the formula in cell C3 shows that May 2, 2019, is 2 weeks before May 16, 2019.
Why does the formula using an absolute reference? Read the article below for a full explanation.
How to add MONTHS to a date in Excel
How to add months to a date is different by adding days/weeks to a date.
The first step to do is to extract the year, month, and day of date by using the YEAR, MONTH, and DAY functions. To add n months after a date, add the n value to the MONTH function result, then arrange them all with the DATE function to find out the date after n months.
The result
1 month and 3 months after May 16, 2019, are June 16, 2019, and August 16, 2019, while 2 months and 5 months before May 16, 2019, are March 16, 2019, and December 16, 2019.
You got the same date even though several months have different days.
How to add YEARS to a date in Excel
To get n years after a date is like to get n months after a date. You must extract the date value using the YEAR, MONTH, and DAY functions.
Add the YEAR function result by to the desired n year value, then arrange them all with the DATE function.
The result
You got the same date and month, the different only in year value.
How to add YEARS, MONTHS, DAYS to a date in Excel
What date is 1 year 4 months 17 days after May 16, 2019?
The first step is to extract the date value using the YEAR, MONTH, and DAY functions.
The second step, add number 1 to the YEAR function result, add number 4 to the MONTH function result and add number 17 to the DAY function result.
The third step arranges all in the DATE function.
The formula
=DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2)
The result
1 year 4 months 17 days after May 16, 2019, is Oct 3, 2020.
**
What date are 2 years 10 months 5 days before May 16, 2019?
The formula
=DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2)
The result
The formula is the same as the previous formula. Use negative numbers to find out the date before a date.
2 years 10 months 5 days before May 16, 2019, is July 11, 2016.