Academic journal article Journal of Accountancy
Calculate Future and Past Dates
Article excerpt
Q. One of my monthly tasks is to calculate contract expiration dates. If I know a contract will expire in nine months from a given date, for example, can Excel figure out the actual expiration date?
A. The EDATE function, when linked with the NOW function, can do that. If you want to know a date nine months from today, use the formula: =EDATE(NOW(),9).
If you want the date nine months ago, use a negative number in the formula, such as: =EDATE(NOW(),-9).
Caveat: If your answer is a five-digit number instead of a date, it means you failed to format the cell correctly. To prepare a cell to exhibit its answer as a date, right-click on the cell to bring up the format screen (see screenshot below) and select the date format you want. The result will be the screenshot on the right.
[ILLUSTRATION OMITTED]
In the unlikely event EDATE fails to work, you probably haven't enabled Analysis ToolPak, which is usually in Excel but by default isn't enabled. …