Ms Office Tune Up

Jul 06

Controlling Your Data Types in Excel 2007

Microsoft Excel, Tips & Tricks Add comments

How Excel decides your data is numeric

Excel automatically interprets any cell that contains only numeric characters as a number. In addition, you can add the following non-numeric characters to a number without causing a problem:

One decimal point (but not two). For example, 42.1 is a number, but 42.1.1 is text.
One or more commas, provided you use them to separate groups of three numbers (like thousands, millions, and so on). Thus 1,200,200 is a valid number, but 1,200,20 is text.
A currency sign ($ for U.S. dollars), provided it’s at the beginning of the number.
A percent symbol at the beginning or end of the number (but not both).
A plus (+) or minus (-) sign before the number. You can also create a negative number by putting it in parentheses. In other words, entering (33) is the same as entering 33.
An equal sign at the start of the cell.

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 5 provides more information about number styles and shows how you can completely control cell formatting.

How Excel decides your data is a date or time

When typing in a date, you have a choice of formats. You can type in a full date (like July 4, 2007) or you can type in an abbreviated date using dashes or slashes (like 7-4-2007 or 7/4/2007), 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 2-5 shows you the options.

Figure 2-5. Whichever way you type in 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 in Section 5.1.

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/2007 and 4/Jul/2007 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 you forget where the cutoff point is, then enter the year as a four-digit number, which prevents any confusion.
If you don’t type in any year at all, Excel automatically assumes you mean the current year. For example, when you enter 7/4, Excel inserts the date 7/4/2007 (assuming it’s currently 2007 on your computer’s internal clock). When you enter a date this way, the year component doesn’t show up in the cell, but it’s still stored in the worksheet (and visible on the formula bar).
Excel understands and displays dates differently depending on the regional settings on your computer. Windows has a setting that determines how your computer interprets dates (see the box in Section 2.2.) On the U.S. system, Month-Day-Year is the standard progression. But on the UK system, Day-Month-Year is the deal. For example, in the U.S., either 11-7-08 or 11/7/08 is shorthand for November 7, 2008. In the UK, the same notations refer to July 11, 2008.

Thus, if your computer has U.S. regional settings turned on, and you type in 11/7/08, then Excel understands it as November 7, 2008, and the formula bar displays 11/7/08.

Note: The way Excel recognizes and displays dates varies according to the regional settings on your computer, but the way Excel stores dates does not. This feature comes in handy when you save a worksheet on one computer and then open it on another computer with different regional settings. Because Excel stores every date the same way, the date information remains accurate on the new computer, and Excel can display it according to the new regional settings.

Typing in times is more straightforward than typing in dates. You simply use numbers, separated by a colon (:). You need to include an hour and minute component at minimum (as in 7:30), but you can also add seconds, milliseconds, and more (as in 7:30:10.10). You can use values from 1 to 24 for the hour part, though if your system’s set to use a 12-hour clock, Excel converts the time accordingly (in other words, 19:30 becomes 7:30 PM). If you want to use the 12-hour clock when you type in a time, follow your time with a space and the letters P or PM (or A or AM).

Finally, you can create cells that have both date and time information. To do so, just type the date portion first, followed by a space, and then the time portion. For example, Excel happily accepts this combo: 7/4/2008 1:30 PM.

Behind the scenes, Excel stores dates as serial numbers. It considers the date January 1, 1900 to be day 1. January 2, 1900 is day 2, and so on, up through the year 9999. This system is quite nifty because if you use Excel to subtract one date from another, then you actually end up calculating the difference in days, which is exactly what you want. On the other hand, it means you can’t enter a date in Excel that’s earlier than January 1, 1900 (if you do, Excel treats your date like text).

Similarly, Excel stores times as fractional numbers from 0 to 1. The number 0 represents 12:00 a.m. (the start of the day) and 0.999 represents 11:59:59 p.m. (the end of the day). As with dates, this system allows you to subtract one time value from another. See Chapter 11 for more information on how to perform calculations that use dates and times.

Technorati Tags: controlling data types