How to Convert a Date Displayed as 12. May into a Real Date in WPS Spreadsheets
Question details
The user needs to convert a text value such as 12. May into a real date in WPS Spreadsheets using the current year, without manually replacing punctuation.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains date-like text entries such as 12. May, and the user needs them recognized as real dates for sorting, filtering, or calculations.
- Observed behavior
- The value remains text instead of a real serial date, so normal date formulas and formatting do not work correctly.
Confirm the date-like text is in one cell and that the month name matches the workbook's regional language settings; DATEVALUE depends on the current locale in WPS Spreadsheets.
Use DATEVALUE with the Current Year and Apply a dd.mm Custom Format
Best when the text uses a month name such as 12. May and you want a real, locale-aware date without correcting the punctuation manually.
DATEVALUE converts a text string that represents a date into a serial date. Adding the current year with YEAR(TODAY()) avoids relying on decimal separators and keeps the conversion current.
Because DATEVALUE is locale-dependent, the month name must be recognized in the workbook's language and regional settings.
Click an empty cell, such as H2, next to the text date in G2.
Type =DATEVALUE(G2&"-"&YEAR(TODAY())) and press Enter.
Select the formula cell and press Ctrl+1, or right-click and choose Format Cells.
In the Number tab, choose Custom and enter dd.mm in the Type box, then click OK.
Convert Text Dates Like 12. May into Real Dates in WPS Spreadsheets
WPS Spreadsheets can directly convert text such as 12. May into a real date by combining DATEVALUE with the current year, then display it with a custom dd.mm format.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the text date, such as 12. May in cell G2.
- 2. Select a helper cell: Click an empty cell in the same row, such as H2, where the converted date will appear.
- 3. Enter the conversion formula: Type =DATEVALUE(G2&"-"&YEAR(TODAY())) and press Enter to create a real date.
- 4. Open Format Cells: Select the formula cell and press Ctrl+1 to open the Format Cells dialog.
- 5. Set the dd.mm format: Choose Custom under Category, enter dd.mm in the Type box, and click OK.
Frequently Asked Questions
Why does my DATEVALUE formula return #VALUE! for 12. May?
DATEVALUE depends on the workbook locale. If the month name is not recognized in your WPS Spreadsheets language and regional settings, use the month name that matches the current locale or adjust the regional language settings.
How do I apply the custom dd.mm format in WPS Spreadsheets?
Select the cell with the formula, press Ctrl+1, go to the Number tab, choose Custom, enter dd.mm in the Type box, and click OK.
Can I use this formula for an entire column of text dates?
Yes. Enter the formula in a helper cell and fill it down, adjusting the source cell reference from G2 to the corresponding row. The custom dd.mm format can also be filled or applied to the range.
Why use YEAR(TODAY()) instead of manually typing the year?
YEAR(TODAY()) automatically supplies the current year, so the conversion stays correct when you open the workbook in a later year and avoids replacing punctuation with decimal separators.
