You can type =TODAY() in a cell or a formula to insert the serial value of the current date. If you type the function in a cell with the General format (which is the default), Excel displays the resulting value in mm/dd/yyyy format. Although this function takes no arguments, you must remember to include the empty parentheses. (Remember that arguments are variables that supply the values a function needs to perform its calculations. You place arguments between the parentheses for functions that require them.)

Similarly, you can type =NOW() in a cell or formula to insert the current date and time. This function also takes no arguments. The result of the function is a serial date and time value that includes an integer (the date) and a decimal value (the time). Excel doesn’t update the value of NOW continuously. If the value of a cell that contains the NOW function isn’t current, you can update the value by recalculating the worksheet. (You recalculate the worksheet by making a new entry or by pressing F9.) Excel also updates the NOW function whenever you open or print the worksheet.

The NOW function is an example of a volatile function—that is, a function whose calculated value is subject to change. Anytime you open a worksheet that contains one or more NOW functions, Excel prompts you to save changes when you close the worksheet, regardless of whether you’ve made any, because the current value of NOW has changed since the last time you used the worksheet. (Another example of a volatile function is RAND.)

The WEEKDAY function returns the day of the week for a specific date and takes the arguments (serial_number, return_type). The serial_number argument can be a serial date value, a reference to a cell that contains either a date function or a serial date value, or text such as 1/27/11 or January 27, 2011. If you use text, be sure to enclose the text in quotation marks. The function returns a number that represents the day of the week on which the specified date falls. The optional return_type argument determines the way the result is displayed. Table 15-2 lists the available return types.

The YEAR, MONTH, and DAY functions return the value of the year, month, and day portions of a serial date value. All three take a single argument, which can be a serial date value, a reference to a cell that contains either a date function or a serial date value, or a text date enclosed in quotation marks. For example, if cell A1 contains the date 3/25/2011, the formula =YEAR(A1) returns the value 2011, the formula =MONTH(A1) returns the value 3, and the formula =DAY(A1) returns the value 25.

Just as the YEAR, MONTH, and DAY functions extract the value of the year, month, and day portions of a serial date value, the HOUR, MINUTE, and SECOND functions extract the value of the hour, minute, and second portions of a serial time value. For example, if cell B1 contains the time 12:15:35 PM, the formula =HOUR(B1) returns the value 12, the formula =MINUTE(B1) returns the value 15, and the formula =SECOND(B1) returns the value 35.

The DATEVALUE function translates a date into a serial value. You must type the single argument as text, using any date from 1/1/1900 to 12/31/9999, and you must add quotation marks around the text. You can enter the argument using any of the built-in date formats; however, if you type the date without a year, Excel uses the current year from your computer’s internal clock. For example, the formula =DATEVALUE(“December 31, 2010”) returns the serial value 40,543.

Similarly, the TIMEVALUE function translates a time into a decimal value. You must type its single argument as text. You can use any of the built-in time formats, but you must add quotation marks around the text. For example, the formula =TIMEVALUE(“4:30 PM”) returns the decimal value 0.6875.

You can use the EDATE function to calculate the exact date that occurs an indicated number of months before or after a given date. It takes the arguments (start_date, months), where start_date is the date you want to use as a starting point and months is an integer value that indicates the number of months before or after the start date. If the months argument is positive, the function returns a date after the start date; if the months argument is negative, the function returns a date before the start date. For example, to find the date that falls exactly 23 months after June 12, 2010, type the formula =EDATE(“6/12/2010”, 23), which returns the value 41041, or May 12, 2012.

The EOMONTH function returns a date that is an indicated number of months before or after a given date. Although EOMONTH is similar to EDATE and takes the same arguments, the value returned is always rounded up to the last day of the month. For example, to calculate the serial date value that is the last day of the month 23 months after June 12, 2010, type the formula =EOMONTH(“6/12/2010”, 23), which returns 41060, or May 31, 2012.

The YEARFRAC function calculates a decimal number that represents the portion of a year that falls between two given dates. This function takes the arguments (start_date, end_date, basis), where start_date and end_date specify the period of time you want to convert to a fractional year. The basis argument is the type of day count you want to use, as described in Table 15-3.

For example, to determine what fraction of a year is represented from 4/12/10 to 12/15/10, you can type the formula =YEARFRAC(“4/12/10”, “12/15/10”). This formula returns 0.675 based on the default 30-day month and 360-day year.

The WORKDAY and NETWORKDAYS functions are invaluable for anyone who calculates payroll and benefits or determines work schedules. Both functions return values based on working days, excluding weekend days. In addition, you can choose whether to include holidays and specify the exact dates. The .INTL forms of these two functions are new in Excel 2010. Each includes an additional argument that allows you to specify exactly what constitutes a weekend.

The WORKDAY function returns the date that is an indicated number of working days before or after a given date. This function takes the arguments (start_date, days, holidays), where start_date is the date you want the function to count from and days is the number of workdays before or after the start date, excluding weekends and holidays. Use a positive value for days to count forward from the start date; use a negative value to count backward. The optional holidays argument can be an array or a reference to a cell range that contains any dates you want to exclude from the calculation. If you leave holidays blank, the function counts all weekdays from the start date. For example, to determine the date that is 100 working days, not counting holidays, from the current date, type the formula =WORKDAY(NOW(),100).

Similarly, the NETWORKDAYS function calculates the number of working days between two given dates. It takes the arguments (start_date, end_date, holidays). For example, to determine the number of working days from January 15, 2011 to June 30, 2011, type the formula =NETWORKDAYS(“1/15/11”, “6/30/11”), which results in a value of 119.

The WORKDAY.INTL and NETWORKDAYS.INTL functions work the same way as their non-INTL counterparts, but each includes an additional weekend argument. These functions use the form (start_date, days, weekend, holidays), where weekend is a number indicating which days you want to omit, as shown in Table 15-4.

There are two ways to specify the weekend argument. The first is to enter one of the numbers shown in Table 15-4, codes that specify days you want to omit. The other method is to enter a 7-digit numeric text string with which you specify individual days of the week (starting with Monday) that you want to include and exclude; a zero (0) indicates a workday, and a one (1) indicates a nonwork (weekend) day. For example, the string “0010100” specifies Wednesdays and Fridays as your “weekend” days.

For example, the formula =NETWORKDAYS.INTL(“1/1/10”,“2/21/11”,“0010100”) returns the value 299, the number of working days between January 1, 2010 and February 21, 2011, skipping all Wednesdays and Fridays.