O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Analysis ToolPak Date Functions

If you activate the Analysis ToolPak add-in (Section 7.2.4), you'll find a number of additional date functions. Some of these, like WORKDAY( ) and NETWORKDAYS( ), are designed to distinguish between business days and weekends in their calculations, and they come in extremely handy if you're a business type. Others, like YEARFRAC( ) and EDATE( ), are useful for a wider range of date-related calculations.

EDATE( ): Calculating Future Dates

The EDATE( ) function (short for Elapsed Date) calculates a future date by adding a certain number of months to a date you supply. You specify two parameters: the starting date, and the number of months you want to move forward (use a negative number to move one or more months backward).

Here's an example that calculates a date one month from today:

=EDATE(NOW( ),1)

The EDATE( ) function would be more useful if it provided a similar ability to advance a date by a set number of days or years. If you need to do that, you'll need to resort to using the DAY( ), MONTH( ), and YEAR( ) functions, in conjunction with the DATE( ) function, as described earlier.

YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates

The YEARFRAC( ) function (short for Year Fraction) lets you take a range of days between two dates in the same year and determine what percentage this represents out of the whole year. For example, if you pay for a monthly fitness club membership and cancel it after a few weeks, this function might be ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required