Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

You can also display the date, combined with text. The formula that follows, for example,

displays text such as
Today is Friday, April 9, 2010:

=”Today is “&TEXT(TODAY(),”dddd, mmmm d, yyyy”)

It’s important to understand that the TODAY function is updated whenever the worksheet is

calculated. For example, if you enter either of the preceding formulas into a worksheet, the formula

displays the current date. When you open the workbook tomorrow, though, it will display the

current date for that day (not the date when you entered the formula).

To enter a date stamp into a cell, press Ctrl+; (semicolon). This enters the date directly

into the cell and does not use a formula. Therefore, the date does not change.

Displaying any date

As explained earlier in this chapter, you can easily enter a date into a cell by simply typing it,

using any of the date formats that Excel recognizes. You can also create a date by using the

DATE function, which takes three arguments: the year, the month, and the day. The following

formula, for example, returns a date comprising the year in cell A1, the month in cell B1, and the day

in cell C1:

=DATE(A1,B1,C1)

The DATE function accepts invalid arguments and adjusts the result accordingly. For

example, this next formula uses 13 as the month argument, and returns January 1, 2010.

The month argument is automatically translated as month 1 of the following year.

=DATE(2009,13,1)

Often, you’ll use the DATE function with other functions as arguments. For example, the formula

that follows uses the YEAR and TODAY functions to return the date for Independence Day (July

4th) of the current year:

=DATE(YEAR(TODAY()),7,4)

The DATEVALUE function converts a text string that looks like a date into a date serial number.

The following formula returns 40412, the date serial number for August 22, 2010:

=DATEVALUE(“8/22/2010”)