How to Install the Excel 2007 Analysis ToolPak
Using Logical Excel Functions in Excel 2007 Formulas
Using Functions in Excel 2007 Formulas

Planning Ahead with Excel 2007's TODAY, DATE, and DATEVALUE Functions

Excel 2007 contains a number of built-in date functions that you can use in your worksheets. Three common date functions are TODAY, DATE, and DATEVALUE, and they can come in very handy when you're trying to create a schedule in Excel.

TODAY

The easiest and most commonly used date function has to be TODAY. This function takes no arguments and is always entered as follows:

=TODAY()

When you enter the TODAY function in a cell by clicking it on the Date & Time command button’s drop-down list on the Ribbon’s Formulas tab or by typing it, Excel returns the current date by using the following Date format:

7/23/2009

Keep in mind that the date inserted into a cell with the TODAY function is not static. Whenever you open a worksheet that contains this function, Excel recalculates the function and updates its contents to the current date. This means that you don’t usually use TODAY to input the current date when you’re doing it for historical purposes (an invoice, for example) and never want it to change.

Excel stores dates as consecutive serial numbers, so you can use some mathematical operators with the TODAY function to designate dates in the past and future. For example, to show the date one week from the current date, you could use the formula =TODAY() + 7.

DATE

The DATE function returns a date serial number for the date specified by the year, month, and day arguments. This function uses the following syntax:

=DATE(year,month,day)

The DATE function comes in handy when you have a worksheet that contains the different parts of the date in separate columns, as shown in the following figure. You can use it to combine the three columns of date information into a single date cell that you can use in sorting and filtering.

Using the DATE function to combine separate date information into a single entry.
Using the DATE function to combine separate date information into a single entry.

DATEVALUE

The DATEVALUE returns the date serial number for a date that’s been entered into the spreadsheet as text so that you can use it in date calculations. This function takes a single argument:

=DATEVALUE(date_text)

Suppose, for example, that you’ve made the following text entry in cell B12:

'5/21/2004

(Remember that when you preface an entry with an apostrophe, Excel inserts that entry as text even if the program would otherwise put it in as a value.) You can then convert this text entry into a date serial number by entering the following formula in cell C12 next door:

=DATEVALUE(B12)

Excel then returns the date serial number, 38128 to cell C12, which you can display as a more intelligible date by formatting it with one of Excel’s Date number formats.

You must convert the DATE and DATEVALUE functions into their calculated date serial numbers in order to sort and filter them. To convert these functions individually, select a cell, press F2 to activate Edit mode, and then press F9 to replace the function with the calculated date serial number; finally, click the Enter button on the Formula bar to insert this serial number into the cell.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Powers and Roots in Excel 2007: The POWER and SQRT Functions
Crunching Numbers with Excel 2007's AVERAGE, MAX, MIN, and MEDIAN Functions
Determining Cell Data Types with Excel 2007's TYPE Function
Simplifying Numbers with Excel 2007's INT and TRUNC Functions
Switching Vertical and Horizontal Data with Excel 2007's TRANSPOSE Function
Advertisement

Inside Dummies.com