How to Display a Capitalized 3-Letter Weekday in Excel
Question details
The user wants to format a date cell to show a three-letter weekday abbreviation with the first letter capitalized, such as 'Mon'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Customizing date formats to ensure the weekday abbreviation is consistently capitalized, regardless of different regional or language settings.
- Observed behavior
- Excel follows the system's language settings by default, which can cause the 'ddd' custom format to display lowercase abbreviations (like 'lun.' in French) instead of capitalized ones.
Verify your operating system's regional and language settings, as Excel natively relies on these defaults to determine how weekday abbreviations are capitalized.
Use the PROPER and TEXT Functions
The most reliable way to force capitalization on a three-letter weekday, regardless of system locale, is by combining the PROPER and TEXT functions in an adjacent cell.
The TEXT function allows you to extract specific parts of a date, while the PROPER function ensures that the very first letter of that extracted text string is capitalized.
Identify the cell containing the original date value. For this example, assume your date is located in cell D2.
Select an empty adjacent cell and type the formula =PROPER(TEXT(D2, "ddd")). The TEXT function extracts the 3-letter day abbreviation, and PROPER handles the capitalization.
Press Enter to see the capitalized result (e.g., 'Mon'). Click and drag the fill handle at the bottom right corner of the cell to apply this formula to the rest of the dates in your column.
Apply a Custom Format (For English Locales)
If your computer is already set to an English locale, you can use Excel's native Custom Format tool to display the abbreviated weekday without writing formulas.
Format Dates Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful cell formatting tools and seamless formula compatibility, making it incredibly easy to extract and format dates exactly how you need them.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date values.
- 2. Select a target cell: Click on an empty cell next to your date data where you want the capitalized day abbreviation to appear.
- 3. Input the formatting formula: Type the formula =PROPER(TEXT(D2, "ddd")) and press Enter to instantly generate the capitalized abbreviation.
- 4. Fill the series: Double-click the small green square at the bottom-right of the cell to automatically fill the formula down your entire data column.
Frequently Asked Questions
Why does the 'ddd' custom format show a lowercase abbreviation?
Excel relies on your operating system's regional and language settings. In certain languages (like French or Spanish), weekdays are not capitalized by default, resulting in lowercase outputs like 'lun'.
Can I display the full capitalized weekday name instead of just three letters?
Yes. By modifying the formula to use four 'd's, such as =PROPER(TEXT(D2, "dddd")), the spreadsheet will return the full capitalized day name like 'Monday'.
Does using the TEXT formula change my underlying data type?
Yes, combining PROPER and TEXT converts your original numeric date value into a text string. If you need to perform further chronological calculations, you should reference the original date cell instead of the newly created text result.
