Microsoft Office Tutorials and References
In Depth Information
Entering dates and times
Entering dates and times
Excel treats dates and times as special types of numeric values. Typically, these values are
formatted so that they appear as dates or times because humans find it much easier to understand
these values if they appear in the correct format.
Excel handles dates by using a serial number system. The earliest date that Excel understands is
January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and
so on. This system makes it easy to deal with dates in formulas. For example, you can enter a
formula to calculate the number of days between two dates.
The date examples in this topic use the U.S. English system. Depending on your
regional settings, entering a date in a format such as June 1, 2010, might be
interpreted as text rather than as a date. In this case, you need to enter the date in a
format that corresponds to your regional date settings — for example, 1 June, 2010.
The following table provides a sampling of the date formats that Excel recognizes. After entering
a date, you can format it to appear in a different date format by using the Number tab of the
Format Cells dialog box.
Date Entered in Cell
Excel’s Interpretation (U.S. Settings)
6-26-10
June 26, 2010
6-26-2010
June 26, 2010
6/26/10
June 26, 2010
6/26/2010
June 26, 2010
6-26/10
June 26, 2010
June 26, 2010
June 26, 2010
Jun 26
June 26 of the current year
June 26
June 26 of the current year
6/26
June 26 of the current year
6-26
June 26 of the current year
Excel is smart, but not perfect, about recognizing dates you enter. For example, Excel does not
recognize any of the following entries as dates: June 1 1010, Jun-1 2010, and Jun-1/2010. Rather, it
interprets these entries as text. If you plan to use references to dates in formulas, make sure that
the date you enter is recognized as a date; otherwise, your formulas will produce incorrect results.
A common problem is that Excel interprets your entry as a date when you intended to enter a
fraction. For example, if you enter the fraction 1/5, Excel interprets it as January 5 of the current
year. The solution is to precede the fraction with an equal sign.
When you work with times, Excel simply extends its date serial number system to include
decimals. In other words, Excel works with times by using fractional days. For example, the date serial
 
Search JabSto ::




Custom Search