Replace Periods with Hyphens in Excel Dates Without Serial Numbers
Question details
Change the displayed separators in Excel dates from periods to hyphens while keeping a two-number date format.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants dates displayed with periods to appear with hyphens without exposing Excel's underlying date serial numbers.
- Observed behavior
- Find and Replace may expose a serial number because the periods are part of the date format rather than characters in the stored value.
Select a date cell and check its number format in the Home tab. This method applies to real Excel dates; dates stored as text require different handling.
Apply a Custom Date Format in Excel
Use this method when the cells contain real Excel dates and you want to change only their appearance.
Excel stores dates as serial numbers. A custom date format changes the display without changing the stored date.
Select the cells containing the dates you want to display with hyphens.
Right-click the selection and choose Format Cells, then open the Number tab.
Select Custom and enter mm-dd in the Type box for a two-digit month followed by a two-digit day. Use dd-mm if you need the day first.
Click OK and check that the selected dates display with hyphens in the intended order.

Display Dates with Hyphens in WPS Spreadsheets
Use a custom number format in WPS Spreadsheets to display real dates with hyphens while retaining their underlying values.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the date cells.
- 2. Open cell formatting: Right-click the selected cells and choose Format Cells.
- 3. Set the date format: On the Number tab, select Custom and enter mm-dd for month-day or dd-mm for day-month.
- 4. Apply and check: Click OK and verify that the dates display with hyphens in the desired order.

Frequently Asked Questions
Why does Find and Replace show a number instead of my date?
Excel stores real dates as serial numbers, and the periods may exist only in the display format. Use Format Cells > Number > Custom to change the separator instead.
How do I keep only the month and day visible?
Select the date cells, open Format Cells > Number > Custom, and enter mm-dd. The year remains in the underlying date but is not displayed.
Why did a date change to 1900-01-01 after formatting?
The underlying value may be a small number that Excel interprets as a date serial number. Check the cell's value; a date format changes its appearance, not the stored value.
Will this method work if my dates are stored as text?
No. Custom date formats apply to numeric date values, not text. Check the cell's number format and convert the text to valid dates before applying the custom format, taking care to use the correct date order.




