How to Calculate a Future Date in Excel

Often when you're working with Microsoft Excel, it's useful to do calculations involving dates. You might want to calculate a future date in Excel when a bill is due or when you're expected to earn interest on an investment. You can use a variety of Excel date functions to do math with dates and times.

Image Credit: Bearinmind/iStock/GettyImages

Excel DateAdd Function

One of the most useful date math functions in Microsoft Excel is called DateAdd.

You can use DateAdd to take an existing date hard-coded into your formula or in another cell and add or subtract days, months or years from it. The function takes a text string description of the interval type you're working with, such as days or months, the number of those intervals you want to add and the starting date.

Use a negative number of intervals to subtract, and check the Excel documentation for how to describe the interval type you want to work with. For example, "d" is used for days and "m" for months.

As an example, the Excel date formula "=DateAdd("d", 5, "5-Jan-15")" will return "10-Jan-15." If you add months to a date, it won't roll into a later month, so while one month after December 30 is January 30, two months after will be February 28 (or February 29 in a leap year). In place of any of the strings, you could use a reference to a cell, such as A5 or B6, as in other Excel formulas.

Basic Date Arithmetic

If you just want to add or subtract from dates, you can use the basic arithmetic operations in a formula. For example, if cell A5 contains "12/15/2017", the formula "=A5+12" will add 12 days to the value in A5, giving 12/27/2017.

You can generally use these sorts of formulas to compute dates based on adding and subtracting days. This can be useful if you want to find a date a certain number of days or weeks before after a given date. Use a negative number of days to look backward.

The EDATE Function

Another useful date math function in Excel is EDATE. It's used for doing arithmetic for months, such as finding the day of the year on the same numeric day a certain number of months ahead.

For example, if cell A5 contains the date 1/1/2017, "EDATE(A5, 5)" will give the day five months in the future, or 6/1/2017. The value will return as a "serial date," which tells you the number of days from January 1st, 1900 to your date. Format the cell containing the result as a date and it will display the date in a familiar format. Use negative numbers to look for dates that occurred before the selected date.

references