Create a custom date or time format
Select the cell or range of cells that you want to format.
How to select a cell or a range- On the Home tab, click the Dialog Box Launcher next to Number.
Tip You can also press CTRL+1 to open the Format Cells dialog box.
- In the Category box, click Date or Time, and then choose the number format that is closest in style to the one you want to create. (When creating custom number formats, it's easier to start from an existing format than it is to start from scratch.)
- In the Category box, click Custom. In the Type box, you should see the format code matching the date or time format you selected in the step 3. The built-in date or time format can't be changed or deleted, so don't worry about overwriting it.
- In the Type box, make the necessary changes to the format. You can use any of the codes in the following tables:
Days, months, and years
| To display | Use this code |
|---|---|
| Months as 1–12 | m |
| Months as 01–12 | mm |
| Months as Jan–Dec | mmm |
| Months as January–December | mmmm |
| Months as the first letter of the month | mmmmm |
| Days as 1–31 | d |
| Days as 01–31 | dd |
| Days as Sun–Sat | ddd |
| Days as Sunday–Saturday | dddd |
| Years as 00–99 | yy |
| Years as 1900–9999 | yyyy |
If you use "m" immediately after the "h" or "hh"
code or immediately before the "ss" code, Excel displays minutes instead
of the month.
Hours, minutes, and seconds
| To display | Use this code |
|---|---|
| Hours as 0–23 | h |
| Hours as 00–23 | hh |
| Minutes as 0–59 | m |
| Minutes as 00–59 | mm |
| Seconds as 0–59 | s |
| Seconds as 00–59 | ss |
| Hours as 4 AM | h AM/PM |
| Time as 4:36 PM | h:mm AM/PM |
| Time as 4:36:03 P | h:mm:ss A/P |
| Elapsed time in hours; for example, 25.02 | [h]:mm |
| Elapsed time in minutes; for example, 63:46 | [mm]:ss |
| Elapsed time in seconds | [ss] |
| Fractions of a second | h:mm:ss.00 |
AM and PM If the
format contains an AM or PM, the hour is based on the 12-hour clock,
where "AM" or "A" indicates times from midnight until noon and "PM" or
"P" indicates times from noon until midnight. Otherwise, the hour is
based on the 24-hour clock. The "m" or "mm" code must appear immediately
after the "h" or "hh" code or immediately before the "ss" code;
otherwise, Excel displays the month instead of minutes.
Creating custom number formats can be tricky if you haven't done it before. For more information about how to create custom number formats, see Create or delete a custom number format.
Top of PageTips for displaying dates or times
- To quickly use the default date or time format, click the cell that contains the date or time, and then press CTRL+SHIFT+# or CTRL+SHIFT+@.
- If a cell displays ##### after you apply date or time formatting to it, the cell probably isn't wide enough to display the data. To expand the column width, double-click the right boundary of the column containing the cells. This automatically resizes the column to fit the number. You can also drag the right boundary until the columns are the size you want.
- When you try to undo a date or time format by selecting General in the Category list, Excel displays a number code. When you enter a date or time again, Excel displays the default date or time format. To enter a specific date or time format, such as January 2010, you can format it as text by selecting Text in the Category list.
- To quickly enter the current date in your worksheet, select any empty cell, and then press CTRL+; (semicolon), and then press ENTER, if necessary. To insert a date that will update to the current date each time you reopen a worksheet or recalculate a formula, type =TODAY() in an empty cell, and then press ENTER.
Comments
Post a Comment