Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.

Follow these steps:

  1. Select the cells you want to format.

  2. Press CTRL+1.

  3. In the Format Cells box, click the Number tab.

  4. In the Category list, click Date.

    Pick Date in the Category list

  5. Under Type, pick a date format. Your format will preview in the Sample box with the first date in your data.

    Note: Date formats that begin with an asterisk (*) will change if you change the regional date and time settings in Control Panel. Formats without an asterisk won’t change.

  6. If you want to use a date format according to how another language displays dates, choose the language in Locale (location).

Tip: Do you have numbers showing up in your cells as #####? It's likely that your cell isn’t wide enough to show the whole number. Try double-clicking the right border of the column that contains the cells with #####. This will resize the column to fit the number. You can also drag the right border of the column to make it any size you want.

If you want to use a format that isn’t in the Type box, you can create your own. The easiest way to do this is to start from a format this is close to what you want.

  1. Select the cells you want to format.

  2. Press CTRL+1.

  3. In the Format Cells box, click the Number tab.

  4. In the Category list, click Date, and then choose a date format you want in Type. You can adjust this format in the last step below.

    Pick Date in the Category list

  5. Go back to the Category list, and choose Custom. Under Type, you’ll see the format code for the date format you chose in the previous step. The built-in date format can’t be changed, so don’t worry about messing it up. The changes you make will only apply to the custom format you’re creating.

  6. In the Type box, make the changes you want using code from the table below.

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’re modifying a format that includes time values, and you use "m" immediately after the "h" or "hh" code or immediately before the "ss" code, Excel displays minutes instead of the month.

  • To quickly use the default date format, click the cell with the date, and then press CTRL+SHIFT+#.

  • If a cell displays ##### after you apply date formatting to it, the cell probably isn’t wide enough to show the whole number. Try double-clicking the right border of the column that contains the cells with #####. This will resize the column to fit the number. You can also drag the right border of the column to make it any size you want.

  • To quickly enter the current date in your worksheet, select any empty cell, press CTRL+; (semicolon), and then press ENTER, if necessary.

  • To enter 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.

When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.

Follow these steps:

  1. Select the cells you want to format.

  2. Press Control+1 or Command+1.

  3. In the Format Cells box, click the Number tab.

  4. In the Category list, click Date.

    Pick Date in the Category list

  5. Under Type, pick a date format. Your format will preview in the Sample box with the first date in your data.

    Note: Date formats that begin with an asterisk (*) will change if you change the regional date and time settings in Control Panel. Formats without an asterisk won’t change.

  6. If you want to use a date format according to how another language displays dates, choose the language in Locale (location).

Tip: Do you have numbers showing up in your cells as #####? It's likely that your cell isn’t wide enough to show the whole number. Try double-clicking the right border of the column that contains the cells with #####. This will resize the column to fit the number. You can also drag the right border of the column to make it any size you want.

If you want to use a format that isn’t in the Type box, you can create your own. The easiest way to do this is to start from a format this is close to what you want.

  1. Select the cells you want to format.

  2. Press Control+1 or Command+1.

  3. In the Format Cells box, click the Number tab.

  4. In the Category list, click Date, and then choose a date format you want in Type. You can adjust this format in the last step below.

    Pick Date in the Category list

  5. Go back to the Category list, and choose Custom. Under Type, you’ll see the format code for the date format you chose in the previous step. The built-in date format can’t be changed, so don’t worry about messing it up. The changes you make will only apply to the custom format you’re creating.

  6. In the Type box, make the changes you want using code from the table below.

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’re modifying a format that includes time values, and you use "m" immediately after the "h" or "hh" code or immediately before the "ss" code, Excel displays minutes instead of the month.

  • To quickly use the default date format, click the cell with the date, and then press CTRL+SHIFT+#.

  • If a cell displays ##### after you apply date formatting to it, the cell probably isn’t wide enough to show the whole number. Try double-clicking the right border of the column that contains the cells with #####. This will resize the column to fit the number. You can also drag the right border of the column to make it any size you want.

  • To quickly enter the current date in your worksheet, select any empty cell, press CTRL+; (semicolon), and then press ENTER, if necessary.

  • To enter 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.

When you type something like 2/2 in a cell, Excel for the web thinks you’re typing a date and shows it as 2-Feb. But you can change the date to be shorter or longer.

To see a short date like 2/2/2013, select the cell, and then click Home > Number Format > Short Date. For a longer date like Saturday, February 02, 2013, pick Long Date instead.

  • If a cell displays ##### after you apply date formatting to it, the cell probably isn’t wide enough to show the whole number. Try dragging the column that contains the cells with #####. This will resize the column to fit the number.

  • To enter 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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

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

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×