How to format dates to uppercase months in Excel?
When working with dates in Excel, you may sometimes need to display just the month portion in uppercase letters for better visibility, reporting standards, or specific formatting requirements. For example, converting a series of dates like 01/15/2024 into the text JAN or JANUARY in uppercase. Excel does not provide a direct built-in format for displaying uppercase months, but with a few practical methods, you can easily achieve this formatting. This article introduces several efficient solutions to help you format dates as uppercase months in Excel, including formula approaches, using Kutools for Excel, and utilizing VBA for automated conversion. Explore the best-fit method for your scenario with the guide below.
Format dates to uppercase months with formulas
Format dates to uppercase months with Kutools for Excel
Format dates to uppercase months with VBA code
Format dates to uppercase months with formulas
Formulas are a convenient way to extract and display date components in Excel. The following methods allow you to convert a date into an uppercase month abbreviation or full name using built-in formulas. This approach is practical if you want the result in a new cell and have dynamic formatting—when the original date changes, the output updates automatically.
1. Select a blank cell (for example, C2) next to the date you want to format to an uppercase month, then enter the following formula in the formula bar:
=UPPER(TEXT(A2,"mmm"))
After pressing Enter, the selected cell will display the three-letter month abbreviation in uppercase (e.g., SEP).
2. To apply this formatting to additional rows, keep cell C2 selected and drag the Fill Handle (the small square at the bottom-right corner of the cell) down alongside your list. Each date will now be represented by its respective uppercase month abbreviation.
You may find this method especially useful in cases where you are working with reports, summary tables, or dashboards that require a standardized format for month names.
- Advantages: The solution is dynamic—changes in the source dates automatically update the result. No add-ins or advanced skills required.
- Limitations: The result is stored in a separate column, not the original. If you need the formatting in place (overwriting original values), consider using a VBA or add-in method.
Notes and tips:
- If you want to display the month with the year (e.g., JAN 24 in uppercase), use the following formula in your desired cell (e.g., C2):
- To show the full month name in uppercase (e.g., JANUARY):
- After entering your desired formula, always press Enter and use the Fill Handle to apply it to the full range of dates.
- If your dates are not recognized as valid Excel dates (e.g., entered as text), the formula will not return the correct result. Ensure source data is formatted as dates.
=UPPER(TEXT(A2,"mmm YY"))
=UPPER(TEXT(A2,"mmmm"))Format dates to uppercase months with Kutools for Excel
The Apply Date Formatting utility provided by Kutools for Excel enables flexible and swift conversion of dates to any preferred format, including uppercase months. This method is helpful for users who prefer a graphical interface instead of editing formulas, and wish to easily format cells in place or quickly customize their date appearance.
1. Select the date cells you want to convert to uppercase months. Then, on the Excel ribbon, go to Kutools > Format > Apply Date Formatting. (If the Kutools tab is not visible, make sure it is installed and enabled.)
2. In the Apply Date Formatting dialog box, choose the date format you need from the Date formatting box. Click OK to confirm.
After completion, the selected cells will be changed to show either month abbreviations or full month names according to your settings.
- Advantages: The Kutools approach is direct, easy to use for users who prefer not to work with formulas, and can process large ranges quickly.
- Tip: After applying the date format, the result is still a date and can be used in other calculations or further formatted.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Format dates to uppercase months with VBA code
If you require an automatic and in-place conversion—replacing your original date values with their corresponding uppercase month names—or want to quickly process a large dataset without helper columns, you can utilize a VBA macro. This approach is highly efficient for bulk operations and gives you full control over the output format.
Applicable scenarios: Use this method if you want to overwrite original data, automate the conversion process, or handle a large number of cells without using additional columns or manual operations.
Precautions: Be aware that running this macro will replace your original dates with uppercase month names, resulting in the loss of original date data in those cells. It is recommended to back up your data before running the macro.
1. Open the VBA editor by clicking Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module. Copy and paste the following code into the module:
Sub ConvertDatesToUppercaseMonths()
Dim WorkRng As Range
Dim Cell As Range
Dim OutputType As Integer
Dim Msg As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range of dates to convert", xTitleId, WorkRng.Address, Type:=8)
Msg = "Enter 1 for 3-letter month (JAN), 2 for full month name (JANUARY):"
OutputType = Application.InputBox(Msg, xTitleId, 1, Type:=1)
For Each Cell In WorkRng
If IsDate(Cell.Value) Then
If OutputType = 2 Then
Cell.Value = UCase(Format(Cell.Value, "mmmm"))
Else
Cell.Value = UCase(Format(Cell.Value, "mmm"))
End If
End If
Next
End Sub2. To use the macro, click the 
Run button, then select the desired range of date cells in the pop-up dialog box. You will be prompted to choose 1 for a 3-letter abbreviation (e.g., JAN) or 2 for the full month name (e.g., JANUARY). After you respond, the macro will replace the original date values with the uppercase month name as specified.
- Advantages: Streamlines converting entire columns or ranges; offers flexibility (abbreviation or full month); runs quickly on large datasets; overwrites original values to minimize manual cleanup.
- Limitations: The original date value is overwritten—make sure you have a backup if you need to keep the original data. Macros must be enabled for the workbook.
- Troubleshooting tip: If the macro appears to do nothing, confirm that the selection includes cells with valid dates, and that macros are enabled and not blocked by Excel security settings.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in