Working with Date and Time Functions
Using the Excel date and time functions, you can perform worksheet calculations quickly and accurately. For example, if you use your worksheet to calculate your company's monthly payroll, you might use the HOUR function to determine the number of hours worked each day and the WEEKDAY function to determine whether employees should be paid at the standard rate (for Monday through Friday) or at the overtime rate (for Saturdays and Sundays).
In the following sections, we'll explore a few of the most useful date and time functions in detail. You can access all 20 of the date and time functions available in Excel by clicking the Date & Time button on the Formulas tab on the Ribbon. For complete information about all the functions Excel has to offer, you can read about a number of tools in "Using the Built-in Function Reference in Excel" on page 487.
Using the TODAY and NOW Functions
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. (You'll remember that arguments are variables that supply the values a function needs to perform its calculations. You place arguments between the parentheses of 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 and close the worksheet, Excel prompts you to save your changes 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.)
For more about the RAND function, see "The RAND and RANDBETWEEN Functions" on page 500.
Using the WEEKDAY Function
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/08 or January 27, 2008. 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.
| If return_type Is... | WEEKDAY Returns... |
|---|---|
| 1 or omitted | A number from 1 through 7 where 1 is Sunday and 7 is Saturday |
| 2 | A number from 1 through 7 where 1 is Monday and 7 is Sunday |
| 3 | A number from 0 through 6 where 0 is Monday and 6 is Sunday |
Open table as spreadsheet | Note | You might want to format a cell containing the WEEKDAY function with a custom day-of-week format, such as dddd. By applying this custom format, you can use the result of the WEEKDAY function in other functions and still have a meaningful display on the screen. |
Using the YEAR, MONTH, and DAY Functions
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/2008, the formula =YEAR(A1) returns the value 2008, the formula =MONTH(A1) returns the value 3, and the formula =DAY(A1) returns the value 25.
Using the HOUR, MINUTE, and SECOND Functions
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.
Using the DATEVALUE and TIMEVALUE Functions
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.
Working with Specialized Date Functions
Excel includes a set of specialized date functions that perform operations such as calculations for the maturity dates of securities, for payroll, and for work schedules.
Using the EDATE and EOMONTH Functions
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, 2008, type the formula =EDATE("6/12/08", 23), which returns the value 40310, or May 12, 2010.
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, 2008, type the formula =EOMONTH("6/12/2003", 23), which returns 40329, or May 31, 2010.
Using the YEARFRAC Function
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.
| If basis Is... | YEARFRAC Returns... |
|---|---|
| 0 (or omitted) | 30/360, or 30 days per month and 360 days per year, as established in the United States by the National Association of Security Dealers (NASD) |
| 1 | Actual/actual, or the actual number of days in the month(s)/actual days in the year |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
For example, to determine what fraction of a year is represented from 4/12/08 to 12/15/08, you can type the formula =YEARFRAC("4/12/04", "12/15/04"). This formula returns 0.675, based on the default 30-day month and 360-day year.
Using the WORKDAY and NETWORKDAYS Functions
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 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, 2008 to June 30, 2008, type the formula =NETWORKDAYS("1/15/08", "6/30/08"), which results in a value of 120.
EAN: 2147483647
Pages: 260