Formula Errors

How to Convert Text Day, Month, and Year into a Date in Excel

Amos Gikunda Sep 30, 2026 868 views

Question details

Combine separate text values representing a day, month, and year into a valid date format without triggering the #VALUE! error.

How to Convert Text Day, Month, and Year into a Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Formatting an inventory export where the best-before date is split into separate day, month, and year text columns, and the final output must be displayed as MMM DD YYYY.
Observed behavior: Using the DATEVALUE function on improperly structured text dates returns a #VALUE! error, preventing correct date formatting.

Before you start

Ensure that your text columns do not contain extraneous leading or trailing spaces, as hidden spaces will cause the DATEVALUE function to fail.

Solution 1Recommended

Use DATEVALUE with Concatenated Cells

Combine the separate day, month, and year columns into a standard date string structure, then convert it to a serial date number.

The DATEVALUE function requires a text string that Excel recognizes as a date. By joining the text cells with a hyphen or slash, you can create a valid string.

Select the target cell

Click on an empty cell where you want the correctly formatted date to appear.

Enter the formula

Type the formula =DATEVALUE(L2&"-"&K2&"-"&M2), assuming L2 contains the month, K2 the day, and M2 the year. Press Enter.

Format the cell

Right-click the cell and select 'Format Cells'. Go to the 'Number' tab, choose 'Custom', type 'mmm dd yyyy' into the Type box, and click OK.

Match Regional Settings: Adjust the order of your cell references in the formula to match your system's regional date settings (e.g., Month-Day-Year vs. Day-Month-Year) so it parses correctly.

Solution 2

Extract Date Parts Using MID and RIGHT Functions

If your original best-before date is stored as a single, consistent text string, extract the components directly using text functions.

Easily Manage Dates with WPS Spreadsheet

Combine Text into Dates Seamlessly in WPS Office

WPS Spreadsheet offers robust text and date manipulation functions, fully supporting DATEVALUE, MID, RIGHT, and custom date formatting. Easily convert raw text exports into workable data.

1. Open your file: Launch WPS Spreadsheet and open your raw inventory export file.
2. Apply the formula: Enter the =DATEVALUE() concatenation formula in a new column to combine your text date fields.
3. Customize formatting: Right-click the result, select 'Format Cells', and apply the custom 'mmm dd yyyy' date format.

Fully compatible with Microsoft Excel DATEVALUE and text extraction formulas.Intuitive Format Cells dialog for applying custom date structures like MMM DD YYYY.Free, lightweight, and fast for handling large inventory exports without lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why does DATEVALUE return a #VALUE! error?

The #VALUE! error occurs when the text string inside the DATEVALUE function does not match any date format recognized by your system settings. You must concatenate the text into a standard layout like "MM-DD-YYYY" or "DD-MM-YYYY" based on your regional configuration.

Can I use the DATE function instead of DATEVALUE?

Yes, if your day, month, and year are numeric, you can simply use =DATE(year_cell, month_cell, day_cell). If your month is represented by text (like "Jan"), the DATE function won't read it directly, making DATEVALUE or a combination of functions necessary.

How do I remove extra spaces from my exported text dates?

You can wrap your individual cell references in the TRIM function. For example, use =DATEVALUE(TRIM(L2)&"-"&TRIM(K2)&"-"&TRIM(M2)) to safely remove any invisible leading or trailing spaces causing formula errors.

Excel & Data Problems