Why Convert Date to Text?

Dates in Excel are fundamentally stored as serial numbers, representing the number of days elapsed since January 1, 1900. This numerical foundation allows for complex calculations, such as determining the number of days between two dates. However, when dates need to be used in specific contexts—such as joining them with other strings (concatenation), preparing data for systems that strictly require text input, or ensuring the date format remains static regardless of spreadsheet regional settings—they must be converted to the text data type.

A common scenario is when a user wants to create a unique identifier that incorporates a date (e.g., “Invoice-25-05-2023”). If the date is left as a standard Excel date, attempting to concatenate it with the string “Invoice-” results in a chaotic outcome, often displaying the underlying serial number instead of the desired date format. Converting the date to a text string ensures that the displayed format—like dd-mm-yyyy or Month Day, Year—is locked in as plain text, suitable for any text-based operation.

Method 1: Using the Format Cells Dialogue (The Pitfall)

One seemingly straightforward method involves using the standard Format Cells dialogue box. This process is often misunderstood because while it changes the cell’s assigned category, it does not perform a true conversion of the underlying value or formatting into a static text string. Here is the process, and why it usually fails to meet the requirement of “not losing the formatting” in the resulting text output.

First, you select the range of cells containing the dates. Next, you right-click the selection and choose “Format Cells.” Within the Number tab, you select the “Text” category. While this designates the cell to treat future inputs as text, it does not retroactively convert the existing date values (which are numerical serial codes) into text based on their current visual appearance. Instead, if you immediately copy or reference this ‘text’ cell, it may still yield the unformatted serial number, completely defeating the purpose of preserving the visible date format. Therefore, this method is primarily useful for ensuring that future data entry is treated as text, not for converting existing, numerically stored dates.

Method 2: Utilizing the TEXT Function (The Preferred Solution)

The definitive and most reliable method for converting a date to a text string while locking in the exact desired format is the TEXT function. This powerful function forces Excel to evaluate the numerical date value, apply a user-defined custom format mask to it, and then output the result as a verifiable text string. This ensures the output is both text and visually correct.

The primary benefit of the TEXT function is its control. Unlike general cell formatting which is dynamic, the TEXT function captures the specified format as part of the formula result. This allows users to generate specific text outputs like “25th May 2023” or “2023 Q2 Data” directly from a single date entry, ensuring consistency and accuracy in reporting or data compilation tasks.

Understanding the TEXT Function Syntax

The syntax for the TEXT function is straightforward, requiring two specific arguments: the value to be converted and the desired format code. The structure is as follows:

=TEXT(value, format_text)

• value: This is the numerical value, typically a cell reference containing the date you wish to convert. Since Excel dates are numbers, this argument accepts the date directly.
• format_text: This is a required argument specified within quotation marks (e.g., “dd/mm/yyyy” or “mmmm yyyy”). This code acts as the template, instructing Excel exactly how to display the date information when converting it to a static text string.

For example, to convert the date in cell A2 into a text string showing the date, month, and four-digit year separated by hyphens, the specific formula used is:

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

This particular formula will convert the date currently residing in cell A2 to a text data type while explicitly utilizing dd-mm-yyyy as the defined date format. Note that by using the TEXT function, you possess the flexibility to specify virtually any recognized Excel date or number format code you desire, giving you complete control over the final output.

Practical Example: Converting Dates Using TEXT

The following detailed example demonstrates the practical application of the TEXT function within an Excel workbook. We start with a list of dates that are currently stored in a standard date format and proceed to convert them into text strings while ensuring the visual format remains identical to the source column.

Suppose we have the following list of dates in Column A of an Excel sheet. These dates currently possess a standard numerical date format (displayed here as dd-mm-yyyy), but they remain numerical values underneath, capable of being formatted differently or yielding serial numbers upon concatenation:

Our objective is to convert each of these dates into a static text string while perfectly preserving the visible format they are currently in (dd-mm-yyyy). This preservation is essential for integration into text-based reports or systems.

Step-by-Step Implementation

To initiate the conversion, we will use the TEXT function in an adjacent column, Column B. Since we are converting the value found in cell A2 using the format code “dd-mm-yyyy,” we type the following formula precisely into cell B2:

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

Once the formula is entered into cell B2, it will immediately display the text representation of the date. The next crucial step is to apply this formula across the entire dataset. We accomplish this by clicking on the fill handle (the small square at the bottom right corner of cell B2) and dragging the formula down to cover every remaining cell corresponding to the dates in Column A. This action dynamically adjusts the cell reference (A2 becomes A3, A4, and so on) for each row.

The resulting output in Column B, after applying the formula across the range, appears as follows:

As evident from the visual result, Column B successfully displays each date from Column A in the intended dd-mm-yyyy format. Crucially, these are no longer numerical date values subject to automatic formatting changes; they are verifiable text strings that will retain their specified appearance permanently.

Verification: Ensuring the Output is True Text

To confirm that the conversion process was successful and that the resulting values in Column B are indeed true text strings (and not just numbers formatted to look like text), we can utilize the built-in ISTEXT function. This diagnostic function returns TRUE if the referenced cell contains a text value, and FALSE otherwise.

We can verify the contents of Column B by typing the following verification formula into cell C2 (assuming C is the next empty column):

=ISTEXT(B2) 

Similar to the previous step, we click and drag this formula down to apply it to every corresponding cell in Column B (i.e., dragging C2 down to C10 in this example). The resulting verification column showcases the following outputs:

Since every cell in the verification column (Column C) returns the boolean value TRUE, this definitively confirms that each corresponding value generated in Column B via the TEXT function is indeed formatted and stored as text. The integrity of the original date format has been successfully maintained throughout the conversion.

Summary of Key Takeaways

Converting dates to text in Excel is a common requirement that demands precision to ensure the visual format is not lost. Relying solely on the “Format Cells” option for existing date values is ineffective as it fails to change the fundamental numerical data type.

The TEXT function provides the necessary control by allowing the user to define the exact format code (like “mm/dd/yyyy” or “dd-mmm-yy”) that should be applied before the value is outputted as a text string. This technique is indispensable for scenarios involving concatenation, standardized reporting, or exporting data to non-Excel systems that require static string representations of dates.

For more advanced usage and detailed format code specifications, users are encouraged to review the official Microsoft documentation for the TEXT function.

The following tutorials explain how to perform other common operations in Excel: