How to Format Excel Dates as March 3 Instead of 3 Mar
Question details
The user wants to format date cells in a spreadsheet to display the full month name followed by the day (e.g., March 3) instead of other default regional formats.
- Product
- Excel / Spreadsheets
- Device & OS
- not provided
- Scenario
- Displaying dates in a simple American English format within a spreadsheet document.
- Observed behavior
- Dates are currently displaying as 3 Mar, 3-3, or other default regional formats instead of the desired full Month and Day format.
Ensure the cells you want to modify are recognized as actual date values by the spreadsheet program, not just plain text. You can test this by changing the cell format to General to see if it converts to a numerical serial value.
Use Custom Number Format (mmmm d)
The most reliable way to force the specific 'March 3' format is by applying a custom formatting code.
Applying a custom format code allows you to override regional settings and define exactly how you want your dates to look.
Highlight the cells, rows, or columns containing the dates you want to format.
Right-click the selected cells and choose 'Format Cells', or use the Number Format dropdown menu on the Home tab and select 'More Number Formats'.
Go to the 'Custom' category. In the 'Type' box, clear any existing text and enter 'mmmm d' for the full month name and day (e.g., March 3). If you prefer an abbreviated month (e.g., Mar 3), enter 'mmm d' instead.
Click 'OK' to apply the new date format to your selected cells.
Change the Locale Configuration
You can achieve this layout using built-in date formats by switching the location settings to English (U.S.).
Format Dates Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers a robust, highly compatible platform for managing and formatting your data. You can apply custom date formats exactly as you would in Microsoft Excel, completely for free.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the dates you wish to modify.
- 2. Select Date Cells: Highlight the cells, columns, or rows you wish to format.
- 3. Open Format Cells: Press the Ctrl+1 keyboard shortcut, or right-click and select 'Format Cells'.
- 4. Set Custom Format: Navigate to the Custom category, enter 'mmmm d' into the Type field, and click OK to apply.
Frequently Asked Questions
Why doesn't the custom format 'mmmm d' work on my dates?
This usually happens if the spreadsheet reads your dates as plain text instead of actual date values. Try re-entering the date or using the 'Text to Columns' tool under the Data tab to convert the text to recognized dates.
How do I add the year to the 'March 3' format?
In the Format Cells dialog under the Custom category, change your format code from 'mmmm d' to 'mmmm d, yyyy'. This will display your dates as 'March 3, 2024'.
Can I apply this date format to an entire column at once?
Yes, you can click the column letter (e.g., 'A') at the top of your sheet to select the entire column, then follow the same steps to apply the custom format to all current and future date entries in that column.
What is the code for a 2-digit day format, like March 03?
To force a leading zero on single-digit days, use the format code 'mmmm dd' in the Custom Type box instead of 'mmmm d'.
