Tips.Net > ExcelTips Home > Formatting > Understanding Date and Time Formatting Codes
Understanding Date and Time Formatting Codes
Summary: Dates, which are stored internally by Excel as numbers, can be formatted in a wide variety of ways. There are built-in formats for dates, but you can also create custom formats that display the dates in any way you want. This tip explains the codes you can use for creating custom formats for dates and times. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
In Excel, dates and times are stored internally as numbers. These are only converted to legible dates and times when you format the cell using a date or time format. The following table lists the formatting codes that are specific to dates and times. You use these codes when you are creating a custom display format (Format | Cells | Number tab | Custom).
| Symbol | Meaning | |
|---|---|---|
| m | Displays the month or minutes as a number without a leading 0 | |
| mm | Displays the month or minutes as a number with a leading 0 | |
| mmm | Displays the month as abbreviated text (Jan, Feb, Mar, and so on) | |
| mmmm | Displays the month as text (January, February, March, and so on) | |
| d | Displays the day of the month as a number without a leading 0 | |
| dd | Displays the day of the month as a number with a leading 0 | |
| ddd | Displays the day of the week as abbreviated text (Sun, Mon, Tue, and so on) | |
| dddd | Displays the day of the week as text (Sunday, Monday, Tuesday, and so on) | |
| yy | Displays the year with two digits | |
| yyyy | Displays the year with four digits | |
| h | Displays the hour without a leading 0 | |
| hh | Displays the hour with a leading 0 | |
| s | Displays the seconds without a leading 0 | |
| ss | Displays the seconds with a leading 0 | |
| [ ] | When surrounding hours, minutes, or seconds place holders, displays hours greater than 24 or minutes and seconds greater than 60 | |
| AM am PM pm A a P p | Uses a 12-hour clock, displaying AM or PM as specified | |
| \ | Forces display of the following character | |
| . (period) | The decimal point. | |
| "text" | Displays the text within the quotes. |
To understand better the effect of the date and time codes, take a look at the following information, which shows several common date and time formats and how they affect numbers.
| Category | Format | Value | Displayed As | |||
|---|---|---|---|---|---|---|
| Date | m/d/yy | 34369 | 2/4/94 | |||
| d-mmm-yy | 34409 | 16-Mar-94 | ||||
| Time | h:mm AM/PM | 0.654166667 | 3:42 PM | |||
| [h]:mm:ss | 2.592673611 | 62:13:27 |
Notice that the numbers shown in the value column of these examples are the internal values stored by Excel for dates and times.
Tip #2179 applies to Microsoft Excel versions: 97 2000 2002 2003
PivotTables Got You Perplexed? Learn the ins and outs of this powerful data-crunching tool. ExcelTips: PivotTables for the Faint of Heart makes it easy.
Save Time!
Have thousands of ExcelTips at your fingertips, on your own system. Answer your own questions or help support others. (more information...)
Helpful Links
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Beauty Tips
Bugs and Pests Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pet Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site