How to Apply Short Date Format in Excel

Let’s say you manage a project and keep track of important deadlines for various tasks. You create an Excel workbook listing each task and its due date. Using the short date format, like “5/16/24,” helps keep the dates neat and easy to read.

For example, if you have a task due on May 16, 2024, you would enter “5/16/24” in the due date column. This makes your workbook look organized and saves space, allowing you to see more tasks at once. When you share this workbook with your team, they can quickly understand when each task is due without confusion.

• Short Date Format – It displays dates in a compact form. It typically looks like “5/16/24,” “05/16/2024”, or 16-May-2024. It shows the month, day, and year with numbers.
• Long Date Format – It displays dates in a more detailed form. For example, “Thursday, May 16, 2024.” It shows the day of the week, month name, day, and year.

There are multiple ways to apply the short-date format in Excel, and in this tutorial, we will examine all those methods in detail.

1. Ribbon Tab: Select the cells with dates, go to the “Home” tab, click the drop-down in the “Number” group, and select “Short Date”.
2. Format Cell Menu: Select the cells and right-click, choose “Format Cells,” go to the “Number” tab, select “Date,” and pick the “Short Date” option.
3. Keyboard Shortcut: Press Ctrl + 1 to open the Format Cells dialog, then go to the “Number” tab, select “Date,” and choose “Short Date”.
4. Custom Format: Select the cells, press Ctrl + 1, choose “Custom,” and enter “mm/dd/yyyy” in the “Type” box.

Apart from these methods, there are two more methods which you can use. And ahead in the tutorial, we will learn all these in detail. So, let’s get started:

Change to Short Date Format from the Home Tab

The short date format is one of the most popular date formats we use in Excel; that is why there is an option on the home tab to apply it. So you use the below steps for this:

1. Select Cells: Use your mouse or keyboard keys to select the cells where you have dates you want to change the format to short dates.
2. Go to Home Tab: Click on the “Home” tab at the top of the Excel window.
3. Number Format Drop Down: In the “Home” tab, go to the “Number” group. Click on the drop-down menu to open the list of formats to apply for.
4. Select Short Date: Click ” Short Date ” From the drop-down menu”.

Selecting the short date will apply a short date format to all the selected cells. If you have more dates where you want to apply the short date format, select the mall and then use the same steps to apply the formatting.

Apply Short Date Format from Right-Click Menu

Using the cell format options, you can also apply short date format on the selected cells. To open this dialogue box, you can use the right-click menu. Here are the steps for this:

1. First, select the cells containing the dates you want to format and right-click open the right-click menu.
2. After that, in the right-click menu, click on “Format Cells”, and in the “Format Cells” window, go to the “Number” tab.
3. Next, in the “Category” list on the left, click “Date.” On the right, select the “Short Date” option (it usually shows as “14-Mar-23” or similar).
4. In the end, click OK to apply the formatting.

Use Keyboard Shortcut to Apply Date Format

You can also open the format cell option from a keyboard shortcut: Ctrl + 1. When you press this keyboard shortcut, it will open the dialog box where you can select the date format you want to apply to the selected cells.

Use a Custom Short Date Format from a Custom Formatting

From all the methods we have discussed and learned here, custom formatting is the most powerful method that allows you to create your own short-date format however you want. You can have the month first or the year first.

1. First, to use this method, select the cells with all the dates for which you want to apply the short date format.
2. After that, Press Ctrl + 1 to open the format cells options or right-click to open the format dialog box.
3. Now, click on the “Number” tab from the dialog box. In the list on the left side, scroll down and select “Custom.”
4. Next, in the “Type” box on the right, type mm/dd/yyyy. This tells Excel to format the dates in a short format, such as month/day/year.
5. In the end, click OK to apply the format.

The best part of this method is that you can create your short-date format.

Use a VBA code to Convert all the Dates into Short Date Format

You can use the code below to use VBA and apply short-date formatting to all the cells in the worksheet.

Sub ApplyShortDateFormat()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the used range of the sheet
    For Each cell In ws.UsedRange
        ' Check if the cell has a date value
        If IsDate(cell.Value) Then
            ' Apply short date format
            cell.NumberFormat = "mm/dd/yyyy"
        End If
    Next cell
End Sub

Press Alt + F11 to open the VBA editor. Click Insert > Module in the VBA editor to create a new module. Paste the VBA code into the module. After that, press Alt + F8 to open the “Macro” dialog box, select ApplyShortDateFormat, and click “Run”.

Use a TEXT Function to Convert the Long Date Format into a Short in a Different Cell

TEXT function can convert a long date format into a short date format in a different cell. The TEXT function changes how a date is shown by applying a specific format. You write it as =TEXT(value, “format”), where “value” is what you want to format and “format” is how you want it to look.

=TEXT(A1,"mm/dd/yyyy")