Skip to main content

Short Date Format in Excel: 5 easy methods

The short date format in Excel is an essential aspect of organizing and presenting your data effectively. It typically includes the day, month, and year in a compact "m/d/yyyy" format, making it ideal for situations where only key date details are necessary.

This tutorial will guide you through five straightforward methods to apply and customize the short date format in Excel, catering to various user needs and preferences. Additionally, we will address commonly asked questions to enhance your Excel experience.

Video: Short Date Format in Excel

Apply short date format in Excel

In this section, we explore four practical methods to apply the short date format in Excel. Whether you're a beginner or an experienced user, these techniques offer versatility and ease for managing date formats in your spreadsheets.

Apply short date format with Number Formatting on the Home tab

Excel's Home tab provides a user-friendly approach for applying the short date format to your date data. This method, utilizing the Number Format options available on the Home tab, is ideal for quickly adjusting date formats without delving into complex menus or commands.

  1. Select the cell(s) containing the dates you want to format.
  2. Go to the Home tab. In the Number group, click the arrow next to the Number Format box.
  3. From the dropdown menu, choose Short Date.

Result

The selected date(s) will be immediately formatted to the short date style. This format will reflect the default date settings of your Windows system.

Note: This method applies the system's default short date format. For a different short date format, you can either use the Format Cells dialog box for additional date formatting options within Excel, or adjust your computer's regional settings to change the default format.

Apply short date format by using shortcut keys

For users who are comfortable with keyboard shortcuts, Excel offers a quick key combination to apply the short date format. This method, similar to the Number Formatting option on the Home tab, applies the system's default short date format.

  1. Select the cell(s) containing the date(s) you wish to format.
  2. Press Ctrl + Shift + # on your keyboard.

Result

The date(s) in your selected cell(s) will transform into the short date format swiftly as demonstrated below.

Note: This method applies the system's default short date format. For a different short date format, you can either use the Format Cells dialog box for additional date formatting options within Excel, or adjust your computer's regional settings to change the default format.

Apply short date format by using the Format Cells dialog box

For those who need more detailed control over formatting, the Format Cells dialog box in Excel is the perfect tool. It allows for a precise application of the short date format.

  1. Select the cell(s) containing the dates you want to format.
  2. Right-click and select Format Cells, or use the shortcut Ctrl + 1.
  3. In the Number tab, click on Date under Category.
  4. Pick your preferred short date format from the Type list and confirm with OK. Tip: The Sample box will display the format preview with the first date in your selected cells.

Result

The selected cell(s) will be updated to display date(s) in your chosen short date format, reflecting the change immediately after you confirm with OK.

formatted result

Notes:

  • In the Format Cells dialog, the default short and long date formats are easily identifiable as they are marked with an asterisk (*). It's important to note that these default formats in the Format Cells box will update immediately to reflect any changes you make to your computer's regional settings.
  • If none of the existing formats align with your particular requirements, you can customize your own date format.
Apply short date format by using the TEXT Function

Excel's TEXT function is a flexible way to format dates, especially useful when integrating dates into text or complex formulas.

  1. Select the cell where you wish to display the formatted short date.
  2. Enter the following formula. 
    =TEXT(B1,"m/d/yyyy")
    Tip: In this formula, B1 refers to the cell that contains the date you want to format. Replace B1 with the actual reference to the cell containing your date.

Result

The date in your specified cell will be quickly converted into the short date format, as shown below.

Note: To tailor the date format to your specific requirements, you have the flexibility to modify the standard "m/d/yyyy" format using various codes. For guidance on which codes to use, please consult the code table provided.

Create and apply a custom date format

In the section on applying a short date format through the Format Cells dialog, we've learned that there are multiple formatting options in the Type list. However, in scenarios where these predefined formats don't meet your specific needs, you can customize your own format, ensuring that your data is presented precisely the way you want it.

The easiest way to do this is to start from an existing format close to what you want in the Format Cells dialog, and then modify it to your needs:

  1. Select the cell(s) containing the dates you want to apply the custom date format.
  2. Right-click and select Format Cells, or use the shortcut Ctrl + 1.
  3. In the Number tab, click on Date under Category.
  4. Pick an existing format under Type that is closest to what you need as your starting point.

  5. Click on the Custom category to modify your chosen format in the Type box, using the codes from the table below. Tip: The Sample box will display the format preview with the first date in your selected cells.

    Use this
    format code    		 To display Description
    m Months as 1–12 Month number without a leading zero
    mm Months as 01–12 Month number with a leading zero
    mmm Months as Jan-Dec Abbreviated month name
    mmmm Months as January-December Full month name
    mmmmm Months as J-D First letter of the month name
    d Days as 1-31 Day of the month without a leading zero
    dd Days as 01-31 Day of the month with a leading zero
    ddd Days as Sun-Sat Abbreviated day of the week
    dddd Days as Sunday-Saturday Full day of the week
    yy Years as 00-99 Two-digit year
    yyyy Years as 1900-9999 Four-digit year

Result

Upon clicking OK in the Format Cells dialog, your chosen date format will be immediately applied to the selected cell(s).

formatted result

Notes:

  • Once a custom date format is created, it's added to the list of custom formats in that workbook. You can reuse it without needing to recreate it.
  • You can use different delimiters (like slashes, dashes, or spaces) when creating a custom date format. For instance, "dd/mm/yyyy", "dd-mm-yyyy", "dd mmmm yyyy".

Frequently asked qestions about short date format in Excel

This section answers common queries about applying date formats in Excel, providing clear guidance and solutions.

How to change the default date format in Excel?

Changing the default date format in Excel is essential when you need dates to consistently appear in a specific style across your documents. This process involves adjusting your computer's regional settings, as Excel pulls its default date format from these system preferences.

  1. Open the Control Panel, and make sure the view is set to Large/Small icons.

    View by

    Tip: If you're unsure how to find the Control Panel, type "control panel" in the search box next to Start on the taskbar. Then, select Control Panel from the list of results.
  2. In the All Control Panel Items window, locate and click on Region.

    Contrl Panel

  3. On the Formats tab of the Region window, adjust the date format as follows:
    1. Select the region by clicking on the arrow in the Format box.
    2. Set your preferred short date format by clicking on the arrow in the Short date box.
    3. Preview the short date to ensure it matches your desired format.

      Setting

      Tip: If the available date formats in the Formats tab don't meet your requirements, you have the option to create custom formats by clicking on the Additional settings button located in the lower right-hand corner. This action opens the Customize Format dialog, where you can switch to the Date tab and enter a custom format for the short date in the corresponding box provided.
How to change date format to adapt to different locales?

When working with data in Excel that includes dates, you may encounter the need to adapt the date format to suit different locales. For instance, in the United States, the common format is month/day/year, while in many European countries, it's day/month/year. Adapting to different locales ensures clarity and prevents misunderstandings.

  1. Select the cell(s) containing the dates you want to convert to another locale.
  2. Right-click and select Format Cells, or use the shortcut Ctrl + 1.
  3. In the Number tab, on the Date category:
    1. Select your preferred Locale (location) option.
    2. Choose a desired format specific to this locale.
    3. Click OK.

      Change Locale

Note: Remember that the date format options are dependent on the system’s locale settings. Different computers might display different formats if their regional settings vary.

Why does Excel show pound signs (#####) instead of a date?

When encountering a series of pound signs (#####) in place of dates within your Excel worksheet, the issue typically stems from the cells being too narrow to display the full date.

To resolve this, you can easily adjust the column width with either of the two methods below.

  • Double-click the right border of the column header, which automatically resizes the column to accommodate the dates.
  • For more precise control over the column width, simply click and drag the right border of the column header to expand it to your desired width.
How to change the date format for an entire column?

To modify the date format across an entire column in Excel, follow these steps:

  1. Select the column by clicking its header, which displays the letter corresponding to the column, such as A, B or C.

    Select an entire column

  2. Then, implement the previously discussed methods to apply your desired format:

Above is all the relevant content related to short date formats in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.