When you type a date or time in a cell, it appears in a default date and time format. This default format is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings in Control Panel. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.
In this article
Display numbers as dates or times
You can format dates and times as you type. For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell. If this isn't what you want—for example, if you would rather show February 2, 2009 or 2/2/09 in the cell—you can choose a different date format in the Format Cells dialog box, as explained in the following procedure. Similarly, if you type 9:30 a or 9:30 p in a cell, Excel will interpret this as a time and display 9:30 AM or 9:30 PM. Again, you can customize the way the time appears in the Format Cells dialog box.
-
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number.
You can also press CTRL+1 to open the Format Cells dialog box.
-
In the Category list, click Date or Time.
-
In the Type list, click the date or time format that you want to use.
Note: Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.
-
To display dates and times in the format of other languages, click the language setting that you want in the Locale (location) box.
The number in the active cell of the selection on the worksheet appears in the Sample box so that you can preview the number formatting options that you selected.
Create a custom date or time format
-
On the Home tab, click the Dialog Box Launcher next to Number.
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.
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 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.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
