Calculate Future And Past Dates

BY STANLEY ZAROWIN

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 left) and select the date format you want. The result will be the screenshot on the right.

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. To turn it on, click on Tools , Add-Ins . One of the options will be Analysis ToolPak . Click on it and then on OK .

If Analysis ToolPak is not among the options, it probably wasn’t installed when Excel or Office was first loaded on your computer. In that case, you’ll have to reinstall Excel from the original disks. When you do, be sure to install the Excel add-ins when the option is presented.

SPONSORED REPORT

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.