Microsoft Office Tutorials and References
In Depth Information
Understanding time serial numbers
TABLE 16.1 (continued)
Entry
Excel Interpretation (U.S. Settings)
June 18
June 18 of the current year
6/18
June 18 of the current year
6-18
June 18 of the current year
18-Jun-2013
June 18, 2013
2013/6/18
June 18, 2013
As you can see in Table 16.1, Excel is rather fl exible when it comes to recognizing dates
entered into a cell. It’s not perfect, however. For example, Excel does not recognize any of
the following entries as dates:
June 18 2013
Jun-18 2013
Jun-18/2013
Rather, it interprets these entries as text. If you plan to use dates in formulas, make sure
that Excel can recognize the date you enter as a date; otherwise, the formulas that refer to
these dates will produce incorrect results.
If you attempt to enter a date that lies outside of the supported date range, Excel
interprets it as text. If you attempt to format a serial number that lies outside the supported
range as a date, the value displays as a series of hash marks ( ######### ).
Searching for Dates
If your worksheet uses many dates, you may need to search for a particular date by using the Find and
Replace dialog box (Home
Find, or Ctrl+F). Excel is rather picky when it
comes to fi nding dates. You must enter the date as it appears in the Formula bar. For example, if a
cell contains a date formatted to display as June 19, 2013, the date appears in the Formula bar using
your system’s short date format (for example, 6/19/2013). Therefore, if you search for the date as it
appears in the cell, Excel won’t fi nd it. But it will fi nd the cell if you search for the date in the format
that appears in the Formula bar.
Editing
Find & Select
Understanding time serial numbers
When you need to work with time values, you extend the Excel date serial number system
to include decimals. In other words, Excel works with times by using fractional days. For
example, the date serial number for June 1, 2013, is 41426. Noon (halfway through the day)
is represented internally as 41426.5.
Search JabSto ::




Custom Search