Date and time number formats

date and time number formats

Most of the standard number formats are fairly self-explanatory, but the Date and Time formats may require a bit of explanation.

To help you understand how they work, do the following:

1. In cell A1, enter 01/01/1900.
2. Select cell A1, and change its number format to Number. The number changes to 1.

Can you figure out why this happened?

Excel assigns a numeric value to each date you enter so that it can create formulas that use dates. For example, you could write a formula that determined what a certain date would be that was a certain number of days before or after the entered date. It starts with 1/1/1900 as its baseline, or 1. Every other date is stored as a number that represents the number of days between it and 1/1/1900. So, for example, 12/31/1900 would be 366. (It was a leap year.)

Now try converting your birthday to a general number format:

1. In cell A2, enter your birthday (mm/dd/yyyy).
2. Format cell A2 using the General number format.

In real-life usage, you seldom need to change between dates and general numbers, so this exercise is just to help you understand what's going on behind-the-scenes.

What about times? To answer this question, do the following:

1. Make sure cell A1 is still formatted with the Number format.
2. In cell A1, enter 1.55.
3. Change cell A1 to the Time format. As you're doing this, select the bottom item from the Type list in the Format Cells dialog box, as shown in Figure 4-7.

Figure 4-7: Select a time format. The bottom two choices show the date as well.

The number 1.55 changes to 1/1/00 13.12. As this experiment indicates, the numbers to the right of the decimal point represent time. The 1 to the left of the decimal still represents 1/1/1900, and the 55 represents 55 percent of the time between midnight and midnight: 1:12 PM, or 13:12 on a 24-hour clock.

This experiment is useful mainly so that you understand how Excel deals with dates and times as numbers; in reality, you'll nearly always be entering dates and times in their normal format, such as 12:30 PM or 13:30:05.