Microsoft Office Tutorials and References
In Depth Information
How Excel Identifies Dates and Times
Adding Different
Types of Data
The most important thing to understand about entering numbers is that when you
choose to add other details like commas or the dollar sign, you’re actually doing
two things at once: You’re entering a value for the cell and you’re setting the format
for the cell, which affects how Excel displays the cell. Chapter 16 provides more
information about number styles and shows how you can completely control cell
How Excel Identifies Dates and Times
When typing in a date, you have a choice of formats. You can type in a full date (like
July 4, 2010 ) or you can type in an abbreviated date using dashes or slashes (like 7-4-
2010 or 7/4/2010 ), which is generally easier. If you enter some numbers formatted
as a date, but the date you entered doesn’t exist (like the 30th day in February or the
13th month), then Excel interprets it as text. Figure 14-33 shows you the options.
Figure 14-33:
Whichever way you
type the date in a cell,
it always appears the
same on the formula
bar (the specific formula
bar display depends on
the regional settings on
your computer, explained
next). To fine-tune the way
the date appears in the
worksheet, you can use
the formatting features
discussed on page 448.
Because you can represent dates a few different ways, working with them can be
tricky, and you’re likely to encounter some unexpected behavior from Excel. Here
are some tips for using dates, trouble-free:
Instead of using a number for the month, you can use a three-letter month
abbreviation, but you must put the month in the middle . In other words, you
can use 7/4/2010 and 4/Jul/2010 interchangeably.
When you use a two-digit year as part of a date, Excel tries to guess whether
the first two digits of the year should be 20 or 19 . When the two-digit year is
from 00 to 29, Excel assumes it belongs to the 21st century. If the year is from
30 to 99, Excel plants it in the 1900s. In other words, Excel translates 7/4/29 into
7/4/2029, while 7/4/30 becomes 7/4/1930.
Tip: If you’re a mere mortal and forget where the cutoff point is, then enter the year as a four-digit
number, which prevents any confusion.
Search JabSto ::

Custom Search