Section 10.5. Analysis ToolPak Date Functions

10.5. 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.

10.5.1. 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.

10.5.2. 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 useful to determine what portion of your money the club should refund (provided they'll spare you from the usual Draconian contract clauses).

The YEARFRAC( ) function requires two parameters: the start date and the end date (which can come from similar or different years). In addition, you can specify a third parameter to indicate how Excel should calculate the fraction. Here's what the function looks like:

 YEARFRAC(start_date, end_date, [basis]) 

The basis must be a number from 0 to 4. The meaning of the number is as follows :

• . Excel performs the calculation in the same way as the DAYS360 function( ). The calculation assumes that every month has 30 days, and every year has 360 days. This system is primarily useful for accounting systems that use 360-day years in their calculations.

• 1 . The calculation gives you the fractional amount of a year that exists between two days. This basis counts the actual number of days in the range, and then divides this figure by the number of days in the year. It correctly takes leap years into account.

• 2 . The calculation counts the number of days between your two dates, but divides this total by 360 instead of the actual number of days in the appropriate year.

• 3 . The calculation counts the number of days between your two dates, but divides this total by 365 instead of the actual number of days in the appropriate year. This provides the same answer as basis 1, assuming the year is not a leap year.

• 4 . The calculation uses the European version of the DAYS360( ) function.

If you don't specify a basis number, Excel will use a basis of 0.

The following formula shows an example that calculates the fraction of the year represented by the range from January 1, 2005 to February 14, 2005:

 =YEARFRAC(DATE(2005,1,1),DATE(2005,2,14),1) 

This formula gives you 0.12, or 12 percent, of the year.

10.5.3. EOMONTH( )

The EOMONTH( ) function (short for End of Month) calculates the last day of any month in any year. However, it doesn't work quite the way you'd expect. EOMONTH( ) is designed so that you can look into the future and answer questions like "Two months from now, what's the last day of the month?" This quirk seems confusing at first, but business people like it because it helps them set payment periods and due dates for invoices.

When using EOMONTH( ), you need to supply two parameters: the starting date, and the number of months you want to look into the future. If you don't want to look into the future, just use 0 for the number of months. Here's what the basic function looks like:

 EOMONTH(start_date, number_of_months) 

The following sample formula takes the date 1/1/2005, moves it to the next month, and then gives you 28 to indicate that the last day in February 2005 is the 28 ^th :

 =EOMONTH(DATE(2005,1,1), 1) 

If you want to find the last day in the current month, just specify 0 for the second parameter. Here's a formula that uses this approach to calculate the last day in the current month:

 =EOMONTH(TODAY( ), 0) 

Like all of Excel's date functions, the EOMONTH( ) function is intelligent enough to handle leap years correctly.

10.5.4. NETWORKDAYS( ): Counting the Number of Business Days

The NETWORKDAYS( ) function counts the number of business days in a given range. It requires two parameters: a start and an end date. In addition, you can supply a third parameter that specifies holidays that Excel shouldn't count. The basic function looks like this:

 =NETWORKDAYS(start_date, end_date, [holidays]) 

The following formula gives you the number 22, which is the number of working days in January 2004:

 =NETWORKDAYS(DATE(2004,1,1), DATE(2004,1,31)) 

You can specify holidays in several ways. If you have a single holiday to specify, you can set it as a date literal. Here's how you would rewrite the earlier example so that January 14, 2004 is designated as a holiday and, therefore, not included in the calculation:

 =NETWORKDAYS(DATE(2004,1,1), DATE(2004,1,31), DATE(2004,1,14)) 

If you want to specify more than one date, you must use a messier syntax, which writes each date as a text string in the month/day/year format. Here's an example that excludes January 2, 2004 and January 5, 2004. Note that you need to surround the holidays with curly braces:

 =NETWORKDAYS(DATE(2004,1,1), DATE(2004,1,31), {"1/2/2004","1/5/2004"}) 

Your final option is to use the holidays specified in a range of cells on the worksheet. For example, if you've entered a holiday schedule in the cells C1:C11, you could use it with the NETWORKDAYS( ) function, as shown here:

 =NETWORKDAYS(DATE(2004,1,1), DATE(2004,1,31), C1:C11) 

10.5.5. WORKDAY( ): Figuring Out When Days Will Fall in the Future

The WORKDAY( ) function allows you to perform date calculations that take workdays, weekends, and holidays into account. Essentially, the WORKDAY( ) function takes a date you specify, and moves it into the future a certain number of business days (skipping over weekends automatically). One of the most common reasons to use WORKDAY( ) is to estimate a due date by taking the current date, and adding the number of business days it should take to complete a given task. The WORKDAY( ) function accepts three parameters: the start date, the number of days you want to move into the future, and any days that Excel should ignore because they represent holidays. The function looks like this:

 WORKDAY(start_date, days, [holidays]) 

You can use the optional holiday parameter to specify days that Excel would ordinarily treat as workdays, but which you don't want to consider as such. You can also supply a reference to a range of cells that contain all the holiday dates.

For example, imagine you want to find out when a project will be completed if you start working today, and it requires 30 days of work. You can use the following formula to estimate the completion date, assuming you don't work on weekends:

 =WORKDAY(TODAY( ), 30) 

As with many of the date functions, you'll need to format the cell so that the value Excel generates (the date serial number) appears as a date.

10.5.6. WEEKNUM( ): Figuring Out in Which Week a Date Falls

The WEEKNUM( ) function accepts a date and gives you a number from 1 to 52 to indicate where the date falls in the year. For example, if the cell that WEEKNUM( ) refers to contained the date 1/1/2005, the function would give you the value of 1 (indicating the first week in the year). If WEEKNUM( ) gives you 52, the date occurs on the last week of the year.