Formatting Issues

Replace Periods with Hyphens in Excel Dates Without Serial Numbers

Jason MillerJason Miller Nov 14, 2026 869 views

Question details

Change the displayed separators in Excel dates from periods to hyphens while keeping a two-number date format.

Replace Periods with Hyphens in Excel Dates Without Serial Numbers
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the dates

Select the cells containing the dates you want to display with hyphens.

2
Open Format Cells

Right-click the selection and choose Format Cells, then open the Number tab.

3
Enter a custom format

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.

4
Apply the format

Click OK and check that the selected dates display with hyphens in the intended order.

Apply a Custom Date Format in Excel
Formatting does not change the date: If a cell displays an unexpected date such as 1900-01-01, check its underlying value. Changing the format cannot correct an incorrect stored value.
Format dates in WPS Spreadsheets

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the date cells.
  2. 2. Open cell formatting: Right-click the selected cells and choose Format Cells.
  3. 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. 4. Apply and check: Click OK and verify that the dates display with hyphens in the desired order.
Change date separators without replacing characters in stored datesChoose month-day or day-month display orderWork with Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formatting controls
microsoft office alternative - wps office

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.