Microsoft Office Tutorials and References
In Depth Information
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:
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.
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:
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: