• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 3. Building Basic Formulas > Date and Time Display Formats

Date and Time Display Formats

If you include dates or times in your worksheets, you need to make sure that they're presented in a readable, unambiguous format. For example, most people would interpret the date 8/5/04 as August 5, 2004. However, in some countries, this date would mean May 8, 2004. Similarly, if you use the time 2:45, do you mean a.m. or p.m.? To avoid these kinds of problems, you can use Excel's built-in date and time formats, listed in Table 3.8.

Table 3.8. Excel's Date and Time Formats
mmmm d, yyyyAugust 3, 2005
h:mm AM/PM3:10 PM
h:mm:ss AM/PM3:10:45 PM
m/d/yy h:mm AM/PM8/23/94 3:10 PM
m/d/yy h:mm8/23/94 15:10

The [h]:[mm]:[ss] format requires a bit more explanation. You use this format when you want to display hours greater than 24 or minutes and seconds greater than 60. For example, suppose that you have an application in which you need to sum several time values (such as the time you've spent working on a project). If you add, say, 10:00 and 15:00, Excel normally shows the total as 1:00 (because, by default, Excel restarts times at 0 when they hit 24:00). To display the result properly (that is, as 25:00), use the format [h]:00.

You use the same methods you used for numeric formats to select date and time formats. In particular, you can specify the date and time format as you input your data. For example, entering Jan-04 automatically formats the cell with the mmm-yy format. Also, you can use the following shortcut keys:

Shortcut KeyFormat
Ctrl+@h:mm AM/PM
Ctrl+;Current date (m/d/yy)
Ctrl+:Current time (h:mm AM/PM)


Excel for the Macintosh uses a different date system than Excel for Windows uses. If you share files between these environments, you need to use Macintosh dates in your Excel for Windows worksheets to maintain the correct dates when you move from one system to another. Select Tools, Options; choose the Calculation tab; and activate the 1904 Date System check box.

Customizing Date and Time Formats

Although the built-in date and time formats are fine for most applications, you might need to create your own custom formats. For example, you might want to display the day of the week (for example, Friday). Custom date and time formats generally are simpler to create than custom numeric formats. There are fewer formatting symbols, and you usually don't need to specify different formats for different conditions. Table 3.9 lists the date and time formatting symbols.

Table 3.9. The Date and Time Formatting Symbols
Date Formats
dDay number without a leading zero (1–31)
ddDay number with a leading zero (01–31)
dddThree-letter day abbreviation (Mon, for example)
ddddFull day name (Monday, for example)
mMonth number without a leading zero (1–12)
mmMonth number with a leading zero (01–12)
mmmThree-letter month abbreviation (Aug, for example)
mmmmFull month name (August, for example)
yyTwo-digit year (00–99)
yyyyFull year (1900–2078)
Time Formats
hHour without a leading zero (0–24)
hhHour with a leading zero (00–24)
mMinute without a leading zero (0–59)
mmMinute with a leading zero (00–59)
sSecond without a leading zero (0–59)
ssSecond with a leading zero (00–59)
AM/PM, am/pm, A/PDisplays the time using a 12-hour clock
/ : . –Symbols used to separate parts of dates or times
[COLOR]Displays the date or time in the color specified
[condition value]Uses conditional statements to specify when the format is to be used

Figure 3.15 shows some examples of custom date and time formats.

Figure 3.15. Sample custom date and time formats.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint