How to Format Dates Before 1900 with Hyphens in WPS Spreadsheets
Question details
The user needs to display historical dates from the 1600s through the 1800s in a DD-MMM-YYYY format with hyphens in a spreadsheet and understand why standard date formatting does not apply before January 1, 1900.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is entering or formatting historical dates before 1900 and wants them to display consistently with hyphens, such as 15-Mar-1650.
- Observed behavior
- Dates before January 1, 1900 do not respond to standard date formatting and may lose hyphens or display inconsistently when a DD-MMM-YYYY format is applied.
Before changing cell formats, identify which pre-1900 dates must be preserved and whether they will be used only for display or also for date calculations and sorting.
Store Pre-1900 Dates as Text to Preserve the DD-MMM-YYYY Format
Use this method when you only need the historical dates to display correctly and do not need chronological date sorting or date-based calculations.
WPS Spreadsheets and Microsoft Excel use a date system that begins on January 1, 1900. Dates before that point are not recognized as real date values, so custom date formats such as DD-MMM-YYYY cannot add hyphens reliably.
Open your workbook in WPS Spreadsheets and select the column or range containing dates from before 1900.
Right-click the selected cells and choose Format Cells from the context menu.
In the Format Cells dialog, select the Text category and click OK.
Type the historical dates in the desired DD-MMM-YYYY format, for example 15-Mar-1650. WPS Spreadsheets will keep the hyphens exactly as entered.
Use a Custom Text-Based Approach or VBA for Pre-1900 Date Calculations
Use this method if you need to compare, sort, or calculate with historical dates before 1900 instead of only displaying them.
Format Pre-1900 Dates as Text in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, change the target cells to Text format, and re-enter historical dates such as 15-Mar-1650. The hyphens remain visible, and the workbook stays fully compatible with Microsoft Excel XLSX files.
- 1. Open WPS Spreadsheets: Launch WPS Office and open your workbook in WPS Spreadsheets.
- 2. Select the target cells: Click the column or selected range that contains the pre-1900 dates.
- 3. Open Format Cells: Right-click the selection and choose Format Cells, or press Ctrl+1.
- 4. Set the format to Text: In the Number tab, select Text and click OK.
- 5. Re-enter dates with hyphens: Type each date in DD-MMM-YYYY format, such as 15-Mar-1650. WPS Spreadsheets preserves the text exactly.
Frequently Asked Questions
Why can’t WPS Spreadsheets apply a date format to dates before January 1, 1900?
Because the spreadsheet date system starts at January 1, 1900. Earlier values are not recognized as true dates, so formats like DD-MMM-YYYY cannot calculate and display the hyphens automatically.
Will storing pre-1900 dates as text still allow chronological sorting?
No. Text sorts alphabetically, not chronologically. For chronological sorting, split the dates into year, month, and day helper columns or use a custom macro.
Can I use these text-based historical dates in formulas?
Not directly as date values. You can use text functions or a custom VBA/macro function to parse and compare them if calculations are required.
Does this date limitation also apply to Microsoft Excel?
Yes. Microsoft Excel has the same January 1, 1900 date system cutoff, so the text-format workaround applies to both Excel and WPS Spreadsheets.
