Excel
Enter and format data
Format data
Format data
Format numbers as dates or times
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 More...Less
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
-
Create a custom date or time format
-
Tips for displaying dates or times
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.
Top of Page
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.
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 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 Communityor get support inCommunities.
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
