Section 11.4. Date and Time Functions | Excel 2007[c] The Missing Manual

11.4. Date and Time Functions

You aren't limited to arithmetic operators for manipulating dates and times. Excel also provides some invaluable ready-to-use functions to help you do things like identify the current date, extract just part of the date, and perform calculations with a date.

11.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time

The TODAY( ) function automatically retrieves the current date. It doesn't require any arguments because it simply examines your computer's internal clock. The TODAY( ) function is extremely useful for creating spreadsheets that continuously update themselves (sometimes called dynamic spreadsheets ). For example, you could create a formula that determines the number of days a payment is overdue. Here's an example:

 =TODAY()-A1

This formula assumes that cell A1 contains the date a payment was due. As a result, it calculates the number of days between the two dates, which shows up as an ordinary number (like 14 for a payment that's two weeks late). Remember, you'll need to display the result as an ordinary number (representing the number of days), not as a date.

The NOW( ) function is similar to the TODAY( ) function, except it retrieves the current date along with the current time. If you use NOW( ) to display a value in a cell that doesn't have any special formatting applied, Excel uses a custom format that shows the date and time (listed in the 24- hour format; for example, 10/5/2007 19:06).

You can use other formats to hide some of this information. For example, a cell you've formatted using the custom number format [h]:mm:ss means you'll see only the time portion 19:06, not the date information. (For more information about custom number formats, see Section 5.1.4.) The following formula shows one handy way to calculate the current time by completely removing the date:

 =NOW()-TODAY()

Remember to format the displayed result as a time value.

Note: Excel recalculates both TODAY( ) and NOW( ) when you reopen a spreadsheet or when you explicitly refresh the worksheet by pressing F9. But, sometimes, you may want to insert the current date and make sure Excel never updates it again. In these cases, you can use the TODAY( ) or NOW( ) function, but you need to convert the result into a static date. Hit F2 to activate edit mode for the cell, and then press F9 to replace the cell with the calculated result. At this point, you'll see the serial number appear in the cell. Finally, press Enter to commit this value.

11.4.2. DATE( ) and TIME( ): Dates and Times in Calculations

As you've learned over the last few chapters, formulas can include cell references and literals , or fixed values. So far, you've seen examples of literal numbers (like 1+1) and literal text. Unfortunately, it's not as easy to insert literal date values into a formula. Although Excel makes it easy to type a single date into a cell (just use a format like 1/7/2007), you can't use the same syntax inside a formula.

Note: If you want to just calculate the difference between dates in two different cells , Section 11.3 shows you how.

For example, if you enter the formula =2/9/2007-1/14/2007 , Excel won't recognize this act as an attempt to calculate the number of days between January 14, 2007 and February 9, 2007. Instead, Excel sees the whole chain of numbers and backward slashes , as well as the minus sign, as one long arithmetic operation involving division. And while you can enter a literal date value by typing in the corresponding serial number (as explained in Section 11.3), this technique's confusing and error-proneunless you're keeping track of the number of days that have elapsed since January 1, 1900.

An easier way to enter literal date values is to use the DATE( ) function. The DATE( ) function is a quick and easy way to insert a date into a formula. DATE( ) accepts three numbers, each of which represents a different component of the date. The function gives you the date's serial number. Here's what it looks like:

 DATE(year, month, day)

In other words, if you enter this formula into a cell:

 =DATE(2008, 1, 1)

Excel displays the date 1/1/2008 . However, you need to watch the formatting you use for the cell. If you use the DATE( ) function in a cell that uses the General number format, Excel automatically adjusts the cell to use the Date number format. However, if you've already applied a specific non-General number format to the cell (like Currency), the DATE( ) function won't change the number format. That means you'll see the familiar date serial number (in this example, 39448 ), which isn't what you want.

Note: For maximum clarity, you should always use a four-digit year with the DATE( ) function. If you enter a two-digit year, Excel assumes you're referring to a year that begins with 19, meaning that 04 becomes 1904, not 2004.

Similarly, you can use the DATE( ) function to create a formula that combines date literals and cell references. Here's an example that determines the number of days between 12/30/2005 and another date (the one contained in cell A1):

 =A1-DATE(2005, 12, 30)

Note: You can use the DATE( ) function to take a year number, month number, and day number from three different cells and calculate the resulting date. However, it's unlikely that you'll find a worksheet that splits date information into more than one cell.

The TIME( ) function performs similar magic with time values. It requires three components : an hour, minute, and second number. If you want, you can use fractional seconds to indicate milliseconds . With this function, you have to enter hours using a 24-hour clock. Here's what the function looks like:

 TIME(hour, minute, second)

For example, the following function calculates how much time exists between now and a deadline at 9:00 PM (21:00 in a 24-hour clock):

 =(TIME(21,0,0)-(NOW()-TODAY( )))*24

As you'll see below, constructing this formula requires several steps. (For a refresher on the precedence rules Excel uses when determining order of operations, see Section 8.1.1.)

First, the formula creates the time literal for 9:00 PM using the TIME( ) function.
Next , the formula determines the current time using the calculation NOW( )DATE( ). This calculation works because it takes the current date and time (the result of the NOW( ) function), and removes the current date portion (the result of the DATE( ) function).
Finally, it calculates the difference between 9:00 PM and the current time. This calculation's result, like all time values, is expressed as a fractional number of days. Consequently, the formula multiplies this number by 24 to display the final result in a format (hours) that humans can more easily understand.

11.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations

The DAY( ), MONTH( ), and YEAR( ) functions are great when you want to calculate dates that fall before or after a certain date you already know. All three functions take a date argument and give you a number representing the day (1 to 31), the month (1 to 12), or the year (1900 to 9999), respectively. For example, if you place the date 1/1/2007 in cell A1, the following formula displays a result of 2007:

 =YEAR(A1)

If you want to use the DAY( ), MONTH( ), and YEAR( ) functions with date literals, you need to use these functions in conjunction with the DATE( ) function. For example, the following formula displays the number 5:

 =MONTH(DATE(2007,05,20))

Note: The DAY( ), MONTH( ), and YEAR( ) functions all require that you put their arguments in the form of a date's underlying serial value, so a formula like =YEAR(1/1/2007 ) won't give you the value you're presumably looking for (i.e., 2007). Instead, Excel calculates the result of the division 1/1/2007 (which is 0.000498), and then passes that to the YEAR( ) function, which has no real meaning. To correct this problem, use the DATE( ) function to create the serial number for the date you need.

The DAY( ), MONTH( ), and YEAR( ) functions really shine when you need to take an existing date and move it forward or backward a set number of days, months, or years . For example, say that, given any date, you want to know what the date will be in two weeks. You could use the DAY( ), MONTH( ), and YEAR( ) functions in conjunction with the DATE( ) function to find out. Here's the formula you'd use ( assuming your base date was in cell A1):

 =DATE(YEAR(A1), MONTH(A1), DAY(A1)+14)

Here's how this formula breaks down, assuming that cell A1 contains the date 5/14/2007. The YEAR( ) and MONTH( ) functions both generate pretty straightforward results for the DATE( ) function's first two arguments: 2007 and 5, respectively. The DAY( ) function gives you a value of 14, which, when added to 14, results in 28, which serves as the third argument in the DATE( ) function. The final result of the formula, therefore, is 5/28/2007.

Impressively, Excel handles these calculations correctly even if the DAY argument's greater than the number of days in the month. For example, if you pass 33 as the third argument to the DATE( ) function, and the current month has only 31 days, Excel simply rolls over to the next month. (Excel knows how many days are in every month.) The DATE( ) function performs a similar trick to increment the year if the second argument you use is greater than 12.

Note: As you may expect, the TIME( ) function, which uses three arguments (hour, minute, second), works similarly. Namely, if you try to pass the function an hour value that's 24 or greater, or a number value that's 60 or greater, TIME( ) automatically rolls these values over. Thus, =TIME(25, 10, 00) results in 1:10 AM.

These DAY( ), MONTH( ), and YEAR( ) functions are also useful for building conditions. For example, you can use MONTH( ) to check if a given date falls on a specific month or day, regardless of the year. The following formula shows a simple example that displays the heading "First of the month" when the day component of the current date is 1. In all other cases, Excel leaves the cell blank.

 =IF(DAY(TODAY())=1,"First of the month")

For much more on how to use conditional logic, see Chapter 13.

UP TO SPEED
Excel's Intentional Date Bug

Most software programmers spend their waking hours struggling to eradicate bugs from their software. So it may come as a bit of a surprise that Microsoft programmers have deliberately inserted at least one bug into all versions of Excel.

People know this bug affectionately as the date leap year bug . As leap year aficionados know, the year 1900 is not a leap year. According to Excel, however, the year 1900 is a leap year. So if you enter the following formula, Excel won't complain, even though February 29th, 1900 isn't an actual date:

 =DATE(1900,2,29)

The reason for this error is compatibility. When Microsoft released Excel, Lotus 1-2-3 was by far the most popular spreadsheet software. Lotus 1-2-3 contained this bug, and by emulating it, Excel could use the same date numbering system, enhancing compatibility.

This bug also means that the days of the week prior to March 1, 1900 are incorrect, and that date calculations that stretch over this date will also be off by one day.

It's unlikely that Microsoft will ever fix this problem. Most conceivable fixes would change the internal numbering for every date after February 28, potentially causing problems with every existing spreadsheet that uses dates. Fortunately, this problem's also fairly benign because few spreadsheets use dates before February 29th, 1900.

11.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations

The HOUR( ), MINUTE( ), and SECOND( ) functions work the same way as the DAY(), MONTH( ), and YEAR( ) functions, except they each generate a number representing the number of hours, minutes, or seconds in a given time. The hour component always uses a 24-hour clock. For example, if you type 9:30 PM into cell A1 (which Excel stores as the serial number 0.89583), the following formula displays the number 21:

 =HOUR(A1)

And this function shows the minute component, which is the number 30:

 =MINUTE(A1)

11.4.5. WEEKDAY( ): Determining the Day of the Week

The WEEKDAY( ) function takes a date, and returns a number that represents which day of the week that date falls on. For example, if the date occurs on Sunday, the number's 1, on Monday it's 2, right up through Saturday, which is 7. If you need this sort of information, the WEEKDAY( ) function is indispensable .

Here's what the function looks like:

 WEEKDAY(date, [return-type])

The first argument in the WEEKDAY( ) function is the date serial number. The WEEKDAY( ) function also accepts an optional second parameter, which you can use to change the numbering system. The second parameter can take one of three preset values: 1, 2, or 3. If you specify number 1, Excel uses the default numbering system described above. Number 2 instructs Excel to start counting at 1 with Monday and end at 7 with Sunday. If you specify number 3, Excel starts counting at 0 with Monday and ends at 6 with Sunday.

Here's a sample formula that returns 7, indicating that January 6, 2005 occurs on a Saturday:

 =WEEKDAY(DATE(2007,1,6))

It's quite easy to change things if you prefer to show the day's name rather than its number. You simply need a little help from the TEXT( ) function (Section 11.1.6), which can convert a number to any number format. In this case, you want to use a custom date format that shows the day name . Here's how:

 =TEXT(WEEKDAY(DATE(2007,1,6)), "dddd")

Here, Excel shows the full day name (Saturday) . You can also tweak the format string to show the day's three-letter abbreviation (like Sat ).

 =TEXT(WEEKDAY(DATE(2007,1,6)), "ddd")

11.4.6. DATEDIF( ): Calculating the Difference Between Dates

You've already learned how to use the DATE( ) function (Section 11.4.2) to subtract one date from another and display the results in terms of days. But what if you want to calculate the difference in years or months? You could try and break the date up into components using the DAY( ), MONTH( ), and YEAR( ) functions, but these types of calculations can get surprisingly complicated. You're better off using Excel's little-known DATEDIF( ) function. Despite this being a useful gem for many date calculations, Excel's own Help tool neglects to cover this function.

DATEDIF( ) accepts three arguments. The first two are the dates that you want to find the difference between. The smaller (older) date goes first, and the other date comes second. The third argument is a string that indicates the way you want to measure the interval, such as in days, months, years, and so on. Table 11-3 explains your options. Remember, as with all string arguments, you need to put quotation marks around whatever value you choose. The formula looks like this:

 DATEDIF(start_date, end_date, interval_type)

Note: Remember, you can't use literal dates in any formula (ever). Instead, use tools like the DATE( ) function to first transform your date into the type of number you can use inside a formula.

Table 11-3. Interval Strings for DATEDIF( )

Code	Meaning	Description
"m"	Months	The number of complete months between the two dates.
"d"	Days	The number of days between the two dates.
"y"	Years	The number of complete years between the two dates.
"ym"	Months Excluding Years	The number of months between the two dates, as if they were in the same year.
"yd"	Days Excluding Years	The number of days between the two dates, as if they were in the same year.
"md"	Days Excluding Months and Years	The number of days between the two dates, as if they were in the same month and the same year.

FREQUENTLY ASKED QUESTION
Solving the DATEDIF( ) Mystery

Why isn't DATEDIF( ) mentioned in Excel's Help ?

DATEDIF( ) is an Excel oddity. It's used by Excel gurus and unknown by just about everyone else. But DATEDIF( ) isn't anything newin fact, it was introduced in Excel 5.0. Microsoft even documented the DATEDIF( ) function in Excel 2000, but the documentation was removed in later versions.

The DATEDIF( ) function has its origins in Lotus 1-2-3. Apparently, Microsoft included it in Excel for compatibility purposes. Microsoft won't say why it has removed the documentation, but it's most likely due to a legal issue. On the other hand, because it has a similar name to the built-in VBA (Visual Basic for Applications) function DateDiff( ), it can create needless confusion. Either way, DATEDIF( ) is unlikely to ever disappear from Excel because it's used in countless spreadsheets to fill a gap that other date functions don't.

If you're still curious , you can read some unofficial DATEDIF( ) documentation at www.cpearson.com/excel/datedif.htm, or you can check out other Excel oddities at http://j-walk.com/ss/excel/odd.

For example, here's how to calculate the number of months that separate a date in the future (stored in cell A1) from the current date:

 =DATEDIF(TODAY(), A1, "m")

Remember that when using the DATEDIF( ) function with the "m" argument, you're counting complete months. That means Excel counts the interval from, say, January 6, 2007 to February 6, 2007, as one month, but the interval from January 6, 2007 to February 5, 2007, is still a day shy, and Excel therefore counts it as zero months. Usually, this is the behavior you want. However, you do have another, somewhat more complicated option, if you want intervals like January 6February 5 to register as one month: use the YEAR( ) and MONTH( ) functions.

For example, here's the DATEDIF( ) approach (which has a result of 0):

 =DATEDIF(DATE(2007,1,6), DATE(2007,2,5), "m")

And here's the YEAR( ) and MONTH( ) approach, which has the result of 1 (it works by subtracting the one month number from the other):

 =MONTH(DATE(2007,2,5))-MONTH(DATE(2007,1,6))

And here's a revised approach that works with dates that aren't in the same year:

 =(YEAR(DATE(2008,2,5))-YEAR(DATE(2007,1,6)))*12 + MONTH(DATE(2008,2,5))-MONTH(DATE(2007,1,6))

Although this formula looks more complicated at first glance, it's really not that difficult. It's so long because Excel calculates the year and month components separately. Once you find the difference in year numbers, Excel multiplies that number by 12, and then adds it to the month component. You then end up with the total number of months.

Unfortunately, this formula assumes that every year is 365 days, which neglects leap years. This formula is probably right most of the time, but it fails in the days just before or after a person's birthday.

11.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers

DATEVALUE( ) and TIMEVALUE( ) convert dates and times that Excel has stored as text into serial date numbers. Of course, in order for this conversion to work, the text must be interpretable as a date or time. So if you type 1-1-2007 or January 1, 2007 into a cell, these functions would be able to convert these values, but if you entered something like 1st January, 07 or 1,1,2007 , you'd get an error message.

Note: Keep in mind that the DATEVALUE( ) and TIMEVALUE( ) functions don't change the formatting of the cell. Therefore, if you want to see something other than the date serial number, you'll need to choose a Date number format.

POWER USERS' CLINIC
Getting a Birthday Right

Now that you've learned about a wide variety of Excel date functions, it probably seems like it wouldn't be very difficult to calculate a person's age based on the current date and his or her birth date. But, in fact, it can be surprisingly tricky. Here's how to get it right.

You can use Excel's support for date subtraction. The following formula is a good first try (assuming the birthday is stored in cell A1):

 =INT((NOW( )-A1)/365)

The YEAR( ), MONTH( ), and DAY( ) functions don't provide a solutionthey all suffer from the same problem of not taking leap years into account. The only real solution is using DATEDIF( ), which does take leap years into account. Here's the DATEDIF( ) formula you'd need:

 =DATEDIF(A1,NOW( ),"y")

You can even get a little fancier with the following formula, which uses some of the text manipulation techniques you saw earlier in this chapter. It displays a person's age in years, months, and days:

 =DATEDIF(A1,NOW( ),"y") & " years, " & DATEDIF(A1,NOW( ),"ym") & " months, " & DATEDIF(A1,NOW( ),"md") & " days"

The rules Excel uses to convert a piece of text to a date with DATEVALUE( ) are the same ones that Excel uses for recognizing dates and time values when you enter them into a cell. To refresh your memory, see Section 2.1.1.3. The only difference is that DATEVALUE( ) and TIMEVALUE( ) ignore the initial apostrophe in a text value.

For example, you can type the following text in cell A1. Remember that the apostrophe tells Excel to treat this entry as text even though it's clearly recognizable as a date:

 '1/1/2004

Now another cell can perform a date calculation by first converting this text to a real date:

 =NOW()-DATEVALUE(A1)

This formula calculates the number of days elapsed since January 1, 2004. Because the result is a number of days, you'll want to use the General number format (not a Date number format).

DATEVALUE( ) and TIMEVALUE( ) also work with literal text strings. That means you can use these functions as an alternative to DATE( ) and TIME( ) if you want to create a date or time literal. The difference? Whereas DATE( ) and TIME( ) create dates and times based on several numeric components you supply, DATEVALUE( ) and TIMEVALUE( ) create dates and times from string literals. Here's an example of TIMEVALUE( ) in action:

 =TIMEVALUE("19:30:00")

This formula converts a static piece of text into the time value 19:30:00 (which Excel represents internally as the fractional number 0.8125). The only drawback to using DATEVALUE( ) and TIMEVALUE( ) is that you'll run into trouble if Excel can't interpret the text you supply. For example, the text string January 1,2007 isn't formatted properly, because there's no space between the comma and the number 2007. This minor glitch is enough to completely stymie the DATEVALUE( ) function. It's much less likely that you'll make an error if you were relying on the DATE( ) function instead.

11.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates

DAYS360( ) gives you the number of days between two dates. Unlike simple date subtraction, however, DAYS360( ) assumes that every month has 30 days (giving the year a total of 360 days). So is this the kind of function you're supposed to use when following some radically alternative calendar system? Sort of. The only reason you'd use DAYS360( ) is if you were using an accounting system that performs its calculations based on 360-day years, which is common in some payroll systems. (Also, some systems calculate investments by assuming 12 months with 30 days each, for a total of 360 days.) Read on if you're still interested.

Here's what the function looks like:

 =DAYS360(start_date, end_date, [European_method])

The smaller (older) date goes first (unless you want a negative number). The second argument is the date following the first date.

The third argument in DAYS360( ) is an optional value that you can set to TRUE to use the European version of the DAYS360( ) calculation. The only difference is how the calculation deals with start or end dates that occur on the 31st day of the month:

In the default U.S. NASD (National Association of Securities Dealers) method, a starting day of 31 changes to 30. An ending day of 31 is handled differently depending on the start day. If the start day's 30 or 31, then the end date changes to day 30 in the same month. If the start day's earlier than 30, then the end date changes from 31 to the first day of the next month.
In the European method, both a starting and ending day of 31 change to 30.

11.4.9. 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, then you need to resort to using the DAY( ), MONTH( ), and YEAR( ) functions, in conjunction with the DATE( ) function, as described earlier.

11.4.10. 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 may help 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 calculation provides the same answer as basis 1, assuming the year isn't a leap year.
4 . The calculation uses the European version of the DAYS360( ) function.

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

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

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

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

11.4.11. EOMONTH( ): Finding the Last Day of Any Month

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/2007, moves it to the next month (February), and then provides the last day of that month (February 28, 2007):

 =EOMONTH(DATE(2007,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 find 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.

11.4.12. 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 23, which is the number of working days in January 2007:

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

You can specify holidays in several ways. When 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 15, 2007 is designated as a holiday and, therefore, not included in the calculation:

 =NETWORKDAYS(DATE(2007,1,1), DATE(2007,1,31), DATE(2007,1,15))

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, 2007 and January 5, 2007. Note that you need to surround the holidays with curly braces:

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

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(2007,1,1), DATE(2007,1,31), C1:C11)

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

11.4.14. 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/2007, then the function gives 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.