Formatting Dates and Times | MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

After you type a date or time in a cell, you can use the Number Format drop-down list on the Home tab on the Ribbon to change its format using the most popular date and time formats, or you can click More Number Formats at the bottom of the list to select any of the built-in formats. In the Format Cells dialog box, select the Date or Time category to display the list of available formats in the Type box on the right. A preview of the format appears in the Sample box in the upper-right corner, as shown in Figure 15-4.

image from book
Figure 15-4: Use the Number tab in the Format Cells dialog box to apply Date and Time formats to cells.

Note

At the top of the list of Date and Time formats you'll see several types that begin with an asterisk (*). These formats respond to changes in the settings available on the Date and Time tabs in the Customize Regional Options dialog box, which you access by opening Control Panel, clicking Regional And Language Options, and then clicking the Customize button on the Formats tab. All other formats remain unaffected by these Control Panel settings.

Most of the Date and Time formats are easy to understand, but a few special formats exist:

The 13:30 and 13:30:55 time formats use the 24-hour (military) time convention.
The 30:55.2 time format displays only minutes and seconds; Excel displays a fraction of a second as a decimal value.
The 37:30:55 time format displays elapsed time.

Note

You can press Ctrl+1 to quickly display the Format Cells dialog box.

Creating Your Own Date and Time Formats

To supplement the standard Date and Time formats, you can create custom formats using the same technique you use for creating custom numeric formats.

For more information about custom formats, see "Creating Custom Number Formats" on page 307.

For example, you can create a format that displays all the available date and time information. The entry 2/24/08 would appear as Tuesday, February 24, 2008 0:00:00.0. To create this format, follow these steps:

Select the cell that contains the date.
Press Ctrl+1 to display the Format Cells dialog box, and if necessary, click the Number tab.
Select the Custom category.
Highlight the entry in the text box at the top of the Type list, and type the following custom format code: dddd mmmm dd, yyyy h:mm:ss.0.
Click OK. Excel stores the new format in the Type list for the Custom category and displays the date using the new format in the selected cell.

You can use the same procedure to display only a portion of the date or the time information available. For example, if you create the format mmmm, Excel displays the date 2/24/2008 as February.

Table 15-1 shows the formatting codes you can use to create custom date and time formats. Be sure to keep two facts in mind. First, Excel assumes that m means months. If, on the other hand, you type the code m immediately after an h, or the code mm immediately after an hh, Excel displays minutes instead of months. Second, if you include one of the codes AM/PM, am/pm, A/P, or a/p in a time format, Excel uses the 12-hour time convention; if you omit these codes, Excel uses the 24-hour (military) time convention.

Table 15-1: Codes for Creating Custom Date and Time Formats
Open table as spreadsheet
Code	Display
General	Number in General (serial value) format.
d	Day number without leading zero (1-31).
dd	Day number with leading zero (01-31).
ddd	Day-of-week abbreviation (Sun-Sat).
dddd	Complete day-of-week name (Sunday-Saturday).
m	Month number without leading zero (1-12).
mm	Month number with leading zero (01-12).
mmm	Month name abbreviation (Jan-Dec).
mmmm	Complete month name (January-December).
yy	Last two digits of year number (00-99).
yyyy	Complete four-digit year number (1900-2078).
h	Hour without leading zero (0-23).
hh	Hour with leading zero (00-23).
m	Minute without leading zero (0-59).
mm	Minute with leading zero (00-59).
s	Second without leading zero (0-59).
ss	Second with leading zero (00-59).
s.0	Second and tenths of a second without leading zero.
s.00	Second without leading zero and hundredths of a second without leading zero.
ss.0	Second without leading zero and tenths of a second with leading zero.
ss.00	Second and hundredths of a second with leading zero.
AM/PM	Time in AM/PM notation.
am/pm	Time in am/pm notation.
A/P	Time in A/P notation.
a/p	Time in a/p notation.
[]	Brackets display the absolute elapsed time when used to enclose a time code, as in [h]. You can use brackets around only the first component of the code.

After you add a custom date or time format to the Type list, you can apply it to any date or time entry. Select the Custom category and select the format you entered from the Type list (new custom formats appear at the bottom of the list), and click OK to apply the format.

Measuring Elapsed Time

You can enclose time codes in brackets, as listed at the bottom of Table 15-1, to display more than 24 hours, more than 60 minutes, or more than 60 seconds in a time value. The brackets must always appear around the first code in the format. Excel provides one built-in elapsed time code, [h]: mm: ss, available in the Custom category Type list. Other valid codes for measuring elapsed time include [mm]:ss and [ss].

Bracketed codes have no effect if you use them in any position of the format other than first. For example, if you use the code h:[mm]:ss, Excel ignores the brackets and displays the time using the regular h:mm:ss format.

Note

One format in the Time category on the Number tab in the Format Cells dialog box represents elapsed time: 37:30:55. This is the same as the [h]:mm:ss format in the Custom category.

Suppose you want to determine the elapsed time between two dates. Type the following formulas in cells A1, A2, and A3, respectively: 11/23/08 13:32, 11/25/08 23:59, and =A2-A1.

If you apply the built-in format 37:30:55 ([h]:mm:ss) to cell A3, the result of the formula is 58:27:00-the elapsed time between the two dates. If you apply the standard 1:30:55 PM (h:mm:ss) format to cell A3 instead, the result is 10:27:00-the difference between the two times. Without the elapsed time format code, Excel ignores the difference in dates.

TROUBLESHOOTING

I can't enter a number of hours greater than 9999.

Suppose you have a worksheet in which you keep a running total of flying time for pilots, using the time formats in Excel. Whenever you try to enter a number of hours greater than 9999 (which isn't uncommon), Excel treats the entry as text. What's wrong?

Nothing is wrong-that's just a built-in limitation of Excel. Here are a couple of ways to work with this limitation:

Use an elapsed time format. In the Cells group on the Home tab, click Format, click Format Cells, click the Number tab if necessary, select the Custom category, and then select [h]:mm:ss in the Type list to apply the one built-in elapsed time format. If you don't need to record seconds, you can delete :ss. Elapsed time formats can store and display an unlimited number of hours.
If you need to enter more than 9999 hours at a time, you'll have to break it into two smaller chunks and type it in two cells.

You should also know that when you type a time greater than 24 hours (even 24:01), Excel adds a date in the formula bar. Unless the number of hours typed exceeds a year's worth, the added date will be sometime in 1900; you'll just have to live with that. You can select the year in the formula bar and type the correct year, but then the year will be displayed in the cell along with the time. Otherwise, the date doesn't show in the cell unless you format it accordingly.