Microsoft Office Tutorials and References
In Depth Information
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 with a function
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,
2013. 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 41508, the date serial number for August 22, 2013:
To view the result of this formula as a date, you need to apply a date number format to the cell.
Be careful when using the DATEVALUE function. A text string that looks like a date in
your country may not look like a date in another country. The preceding example works
fine if your system is set for U.S. date formats, but it returns an error for other regional
date formats because Excel is looking for the eighth day of the 22nd month!
Generating a series of dates