How to Convert a Month Name Like April to 01-Apr-2020 in WPS Office Spreadsheets or VBA
Question details
The user needs to convert a text month name such as "April" into a date value showing the first day of a specified year, formatted as dd-mmm-yyyy.
- Product
- WPS Office Spreadsheets / Excel
- Device & OS
- not provided
- Scenario
- A month name is stored in a worksheet cell or VBA variable, and the user wants to turn it into the date 01-Apr-2020 for display or further calculations.
- Observed behavior
- The month name remains text and is not recognized as a date; after conversion it should display or compute as 01-Apr-2020.
Confirm that the month name, for example April, is stored as text in the cell or variable and decide which year should be used. In WPS Office Spreadsheets, keep the target year in the formula or replace 2020 with the year you need.
Use the TEXT and DATEVALUE Formula in a Spreadsheet Cell
Best for most users who want a quick worksheet formula without macros; it converts a month name in a cell to dd-mmm-yyyy.
This method builds a complete date text string such as "1 April 2020", converts it with DATEVALUE, and then formats it with TEXT. Use DATEVALUE alone if you need a numeric date that can be used in calculations.
Click an empty cell, such as B2, where you want the converted date to appear.
Type =TEXT(DATEVALUE("1 "&A2&" 2020"),"dd-mmm-yyyy") and press Enter. Replace A2 with the cell that contains the month name.
To calculate with the date later, enter =DATEVALUE("1 "&A2&" 2020"), then select the cell, click the Home tab, open Number Format, and choose More Number Formats > Custom to enter dd-mmm-yyyy.
Convert the Month Name with a VBA Macro
Use this when you want to automate the conversion or when the month name is held in a VBA variable.
Convert Month Names to Dates with WPS Office Spreadsheets
Use Excel-compatible TEXT and DATEVALUE formulas or VBA in WPS Office Spreadsheets to turn a month name like April into 01-Apr-2020.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the month name.
- 2. Enter the conversion formula: Select an empty result cell and enter =TEXT(DATEVALUE("1 "&A2&" 2020"),"dd-mmm-yyyy"), replacing A2 with the month-name cell.
- 3. Create a real date for calculations: Enter =DATEVALUE("1 "&A2&" 2020") and set the cell number format to Custom > dd-mmm-yyyy so the result is a true date.
- 4. Automate with a VBA macro if needed: Press Alt+F11, insert a module, and use DateValue("1 " & str & ", 2020") wrapped in Format(..., "dd-mmm-yyyy") for repeated conversion.
Frequently Asked Questions
Can I use this formula for any month name stored in a cell?
Yes. Replace A2 with the cell that contains the month name, such as April, and the DATEVALUE function will create the first day of that month for the year in the formula.
How do I convert the month name into a real date instead of formatted text?
Use =DATEVALUE("1 "&A2&" 2020") and then format the cell with Custom format dd-mmm-yyyy. This stores a numeric date that can be used in calculations and sorting.
Can I change the year from 2020 to another year?
Yes. Replace 2020 in the formula with the year you need, for example =TEXT(DATEVALUE("1 "&A2&" 2023"),"dd-mmm-yyyy").
Does this work in WPS Office Spreadsheets as well as Microsoft Excel?
Yes. WPS Office Spreadsheets supports Excel-compatible TEXT and DATEVALUE formulas and .xlsx files, so the same worksheet formula works in both applications.
