Format numbers as dates or times (2024)

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.

  1. On the Home tab, in the Number group, click the Dialog Box Launcher next to Number.

    Format numbers as dates or times (1)

    You can also press CTRL+1 to open the Format Cells dialog box.

  2. In the Category list, click Date or Time.

    Format numbers as dates or times (2)

  3. 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.

  4. To display dates and times in the format of other languages, click the language setting that you want in the Locale (location) box.

    Format numbers as dates or times (3)

    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.

    Format numbers as dates or times (4)

Top of Page

Create a custom date or time format

  1. On the Home tab, click the Dialog Box Launcher next to Number.

    Format numbers as dates or times (5)

    You can also press CTRL+1 to open the Format Cells dialog box.

  2. 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.)

  3. 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.

  4. 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.

Format numbers as dates or times (6)

Microsoft 365 subscription benefits

Format numbers as dates or times (7)

Microsoft 365 training

Format numbers as dates or times (8)

Microsoft security

Format numbers as dates or times (9)

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Format numbers as dates or times (10)

Ask the Microsoft Community

Format numbers as dates or times (11)

Microsoft Tech Community

Format numbers as dates or times (2024)

References

Top Articles
Latest Posts
Article information

Author: Ouida Strosin DO

Last Updated:

Views: 6224

Rating: 4.6 / 5 (56 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Ouida Strosin DO

Birthday: 1995-04-27

Address: Suite 927 930 Kilback Radial, Candidaville, TN 87795

Phone: +8561498978366

Job: Legacy Manufacturing Specialist

Hobby: Singing, Mountain biking, Water sports, Water sports, Taxidermy, Polo, Pet

Introduction: My name is Ouida Strosin DO, I am a precious, combative, spotless, modern, spotless, beautiful, precious person who loves writing and wants to share my knowledge and understanding with you.