Solved: Why doesn't changing a cell format to Text convert existing date/time values to text in Excel

📌 The Problem Explained

In Excel, dates and times are stored as serial numbers. For example, the date January 1, 2024, is actually stored as the number 45292. When you change a cell’s format to Text via the Home > Number Format dropdown, Excel simply changes the “mask” on the cell without re-processing the data inside.

If the date was already there, changing the format to Text often results in one of two frustrating outcomes:

1. The Serial Number appears: The date suddenly turns into a five-digit number (e.g., 45292).
2. Nothing changes: The date still looks like a date, but if you try to use text-based functions like LEFT() or CONCATENATE(), they behave as if they are looking at the underlying serial number rather than the text you see.

Excel only applies the “Text” rule to new data entered after the format change. To convert existing dates, you must force Excel to re-evaluate the value.

💡 The Quick Solution

The most reliable way to convert an existing date to a specific text string is using the TEXT function:

=TEXT(A2, "mm/dd/yyyy")

Alternatively, if you want to convert a whole column in place without formulas, use the Text to Columns trick:

1. Select the column.
2. Go to Data > Text to Columns.
3. Click Finish immediately (or select Text in Step 3).

🛠️ Step-by-Step Breakdown

Method 1: Using the TEXT Function (Recommended)

This is the best method because it gives you total control over how the text will look (e.g., “Jan-2024” vs “01/01/24”).

Step	Action	Description
1	Identify Source	Locate the cell containing your date (e.g., cell A2).
2	Enter Formula	In an adjacent empty cell, type =TEXT(A2, "yyyy-mm-dd").
3	Choose Format	Use format codes like DDDD for the day name or MMM for the short month.
4	Copy/Paste Values	Copy the formula results and use Right-Click > Paste Values to remove the formula.

Method 2: The “Text to Columns” Force

If you have a massive list and don’t want to use a helper column, use this UI-based approach to force Excel to “re-type” the data.

Step	UI Path	Instruction
1	Select Range	Highlight the column of dates you wish to convert.
2	Data > Text to Columns	Open the Convert Text to Columns Wizard.
3	Next > Next	Click Next through Step 1 and Step 2 (keep defaults).
4	Column Data Format	In Step 3, select the Text radio button.
5	Finish	Click Finish. Excel re-writes the dates as literal text strings.

⚠️ Common Mistakes to Avoid

• Mistake: Changing the format and expecting immediate results. Changing a cell to Text format does not “refresh” the content. You must either re-type the value, double-click the cell and press Enter, or use the methods mentioned above to trigger the change.
• Mistake: Using the wrong case in the TEXT function. In Excel format codes, m stands for Month, whereas h stands for Hour and s for Seconds. However, if you are working with time, m used immediately after h (e.g., [h]:mm) will represent Minutes. Always double-check your format string to ensure your months don’t turn into minutes!