Create A Custom Date Or Time Format


Select the cell or range of cells that you want to format.

ShowHow to select a cell or a range On the Home tab, click the Dialog Box Launcher next to Number.Ribbon Image 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 Page Top of Page

Tips 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 [email protected] 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.