How to format date to show abbreviated day of week or month in Excel?

Microsoft Excel’s Format Cells feature allows you to display dates in a variety of custom formats, including abbreviated forms for days of the week or months. This approach is particularly suitable when you want to change the visual representation of dates directly in their original cells, without creating new columns or altering the underlying values.

1. Select the cells containing the dates you wish to display as abbreviated days of the week or months. Then, right-click on the selection and choose Format Cells from the context menu. See screenshot:

2. In the Format Cells dialog box, navigate to the Number tab. Click to select Custom from the Category list. Next, enter your desired format code in the Type field.

Note:

• To display dates as abbreviated day of week (e.g., "Mon", "Tue"), type DDD.
• To show abbreviated months (e.g., "Jan", "Feb"), type MMM.

• If you only want the first initial (e.g., "F" for Friday, or "J" for January), Excel’s built-in format doesn’t support single-letter abbreviations for days or months directly; you can achieve this with formulas (see below) or VBA.

3. After setting the custom format, click the OK button to confirm and apply the new date formatting to your selection.

The selected dates will immediately be displayed in the abbreviated style you chose, either as three-letter weekday names or month names. Your data is now visually compact while the cell values remain as original dates. See screenshot:

Practical Tip: The Format Cells method only changes visual appearance—your data remains recognized as dates by Excel and can still be used in calculations, sorting, or filtering. If you need to convert the date to actual text values (for exporting, or use outside Excel’s date context), consider using one of the formula solutions below.

Possible Issue: If your selected cells contain values that are not recognized as dates by Excel, the custom formatting may not display as intended. Confirm cell contents are stored in date format (not as text) before formatting.

Kutools for Excel includes an Apply Date Formatting utility, which makes it easy to display dates as abbreviated day of week or abbreviated month with just a few clicks. This is particularly useful if you frequently need to switch between various date formats or want to ensure consistency across reports, without remembering custom format codes. The tool is suitable both for occasional formatting tasks and for users who want to simplify date formatting workflows in complex spreadsheets.

1. Select the range of dates you want to format, then go to Kutools > Format > Apply Date Formatting.

2. In the appearing Apply Date Formatting dialog box, choose an option like Mar (for an abbreviated month) or Wed (for an abbreviated day of week) from the Date formatting list, and then click OK.

The selected dates will be instantly displayed in the new format according to your selection. This method eliminates guesswork and provides a quick preview of the result, which is particularly handy for users not familiar with Excel's custom date code system.

Advantages:

• Intuitive graphical interface allows quick selection among multiple date format presets with preview
• No need to remember or type custom format codes manually
• Efficient for batch-formatting large ranges or multiple non-contiguous selections

If you prefer to output abbreviated days or months as text in new columns—such as for exporting, reporting, or meeting specific data structure requirements—Excel formulas provide a direct and highly customizable approach. This method also allows for one-letter abbreviations or custom abbreviation patterns that are not available with standard formatting.

1. Select a blank cell adjacent to the first date cell you want to convert (for example, if your first date is in cell A2, select cell C2). Enter the following formula to display the abbreviated month:

=TEXT(A2,"MMM")

This will display the three-letter abbreviation for the month (e.g., "Jan").

After entering the formula, press Enter. Then copy the formula down by dragging the fill handle to cover all needed rows.

Further variations and tips:

• For a one-letter abbreviation of the month (e.g., "J" for January), use this formula:

=LEFT(TEXT(A2,"MMM"),1)

• For the three-letter abbreviated day of the week (e.g., "Fri" for Friday), enter:

=TEXT(A2,"DDD")

• For a custom one- or two-letter weekday abbreviation, apply:

=CHOOSE(WEEKDAY(A2), "Su", "M", "Tu", "W", "Th", "F", "Sa")

How to use: After entering any of these formulas, copy the result down alongside your list by dragging the formula's fill handle. Adjust the cell references if your dates are in different columns.

Notes:

• Formulas with TEXT() always return text, even if the source is a date value.
• If you see error messages, ensure your source data in column A contains valid date values (not text-formatted numbers or empty strings).
• Custom abbreviations are flexible with CHOOSE(), so you can swap in your own preferred patterns (e.g., "S", "M", "T", etc.).

Once these formulas are filled down alongside your date column, you will have a new column containing only the desired abbreviation for each date. See screenshot:

For advanced users or those who want to automate this process for large datasets, VBA provides a powerful solution for converting dates into abbreviated forms directly or in-place. This is particularly suitable for repetitive tasks or batch-processing, where you want to streamline date abbreviation without manual formula entry or formatting each time.

Caution: Always back up your data before running VBA scripts, as batch operations may overwrite original values.

To use the VBA solution, follow these instructions:

1. Click Developer Tools > Visual Basic to open the VBA editor.
2. In the editor window, click Insert > Module.
3. Paste the following code into the new module:

Sub AbbreviateDate()
    Dim cell As Range
    Dim WorkRng As Range
    Dim OutputType As String
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to abbreviate dates", xTitleId, WorkRng.Address, Type:=8)
    OutputType = Application.InputBox("Enter 'D' for day abbreviation or 'M' for month abbreviation", xTitleId, "D", Type:=2)
    For Each cell In WorkRng
        If IsDate(cell.Value) Then
            If OutputType = "M" Or OutputType = "m" Then
                cell.Value = Format(cell.Value, "MMM")
            ElseIf OutputType = "D" Or OutputType = "d" Then
                cell.Value = Format(cell.Value, "DDD")
            End If
        End If
    Next
End Sub

After inserting the code:
1. Close the VBA editor.
2. Select the range of dates you want to abbreviate in Excel.
3. Press Alt + F8, choose AbbreviateDate, and click Run.
4. Follow the prompts to select the range and type D (for day of week) or M (for month) abbreviations.

Advantages:

• Fast batch-processing and no need for intermediate columns or formulas
• Customizable—can be expanded to support further styles such as single-letter abbreviations

• Script overwrites original date values in-place; use with caution or run on data copies

Convert date to weekday name or month name in Excel

Change multiple dates to day of week in excel