How to Display Only the Day of the Month in Excel (Keep Actual Dates)
Question details
The user wants to format a date counter to visually display only the numerical day of the month (1 to 31) while preserving the complete calendar date data for sequential additions and formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a sequential date schedule where displaying the full month and year clutters the spreadsheet, but the underlying date values are still required for logical calculations.
- Observed behavior
- Using text conversion or manual number counters removes the actual month and year data, breaking other formulas that rely on complete dates.
Select the cells you wish to modify and ensure they contain valid date values rather than text strings, so the formatting rules can properly apply to the underlying serial dates.
Use Custom Number Formatting to Display Only the Day
This is the most robust method, as it alters only the visual appearance of the cell without changing the actual date value stored in the background.
By applying a custom format, you command the spreadsheet software to mask the month and year. Any subsequent formula referencing this cell will still recognize it as a complete calendar date.
Highlight the range of cells containing the dates you want to format.
Right-click the selected area and choose "Format Cells" from the context menu, or press the Ctrl+1 keyboard shortcut.
Navigate to the "Number" tab in the dialog box and select "Custom" at the bottom of the Category list.
In the "Type" input box, enter a single 'd' to display the day without leading zeros (e.g., 5), or 'dd' for a two-digit day (e.g., 05). Click OK to apply.
Extract the Day Number Using the DAY Function
Use this method if you need to pull the day integer into an entirely separate cell for numerical calculations.
Format Dates Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides a powerful, highly compatible environment to handle complex date formatting with ease. You can easily switch date displays to show just the day while retaining the crucial underlying calendar data for sophisticated scheduling.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your date tracker.
- 2. Highlight Target Cells: Select the specific dates you want to display as days only.
- 3. Access Format Cells: Press Ctrl+1 to open the Format Cells properties window.
- 4. Set Custom Format: Go to Number > Custom, type 'd' in the formatting field, and click OK to finalize.
Frequently Asked Questions
Will changing the date format to show only the day affect my formulas?
No. Custom formatting only alters how the date looks on your screen. The underlying serial date value, which includes the accurate month and year, remains completely intact for any formulas or calculations you run.
How do I display a leading zero for single-digit days like 01 or 09?
To force a two-digit display, open the Format Cells dialog, go to Custom, and type 'dd' instead of a single 'd' into the Type box. This adds a zero before days 1 through 9.
Can I display the day of the week instead of the calendar number?
Yes. In the Custom Format Type box, use 'ddd' to show the abbreviated weekday name (e.g., Mon, Tue) or 'dddd' to display the full weekday name (e.g., Monday, Tuesday).
Why did my date turn into a random 5-digit number when formatting?
Spreadsheets store dates as sequential serial numbers starting from January 1, 1900. If your cell format accidentally shifted to 'General' or a standard 'Number', it will display this raw serial number. Simply change the format back to 'Short Date' or your custom 'd' rule.
