Convert Date to Text in Excel

How to Convert Date to Text in Excel?

When we work in excel often, we deal with numbers, text, date format. Excel completely works on numbers, and it will reflect the values based on the formatting you give. Date & Time in excelTime In ExcelTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more stored as numbers and converted to readable values based on the formatting.

Look at the below example, the value in cell A1 is 43388, but if you format it to date, it will show you value as 15-10-2018.

You can download this Convert Date to Text Excel Template here – Convert Date to Text Excel Template

data to text 1

Right-click on the cell and select the Format Cells option.

data to text 2

Select the date option and select the below option.

data to text 3

Now, the result will be as below.

date example 4

So, excel will reflect the numbers based on the formatting you give.

Where can you Convert Date to Text in Excel?

Now, let us see some examples where you can convert Date to Text in Excel.

When we need to convert Date to Text in ExcelDate To Text In ExcelYou can convert Date to Text in Excel through the most commonly used method, i.e., the text function or by using: Text-to-Column option, Copy Paste Method and VBA. read more, we need to use the TEXT function in excel. As I already mentioned, time & date in Excel is stored as numbers. However, sometimes we may require showing it as the text string. In such cases, we can use the TEXT function.

data to text 4
  • TEXT function consists of VALUE & FORMAT_TEXT.
  • VALUE: Which value you need to convert. It is simply the targeted cell. This could be a number, reference cell that contains a number.
  • FORMAT_TEXT: The format you need to give to the cell, i.e., the targeted cell.

There are multiple date formats available in excel. The below table will give a better idea about the different formats and results.

date to text 1
date to text 2
date to text 3

Example #1 – Convert Date to Text in Excel using “TEXT” Function

From cell A2 to A10, we have the below values and convert them to the Date from B2 to B10.

date to text example 1

In order to convert them to date format, in cell B2 write the below formula.

=TEXT(A3,”dd-mm-yyyy”)

date to text example 1-1

Press enter and drag the formula

date to text example 1-2

Example #2 – Convert Date to Text in Excel using “TEXT” Function

Take the below data and join the two columns (A2 & B2) together. For example, get the result as Shwetha Menon’s Birthdate is 14 Dec 2002.

date to text example 2

Step 1:

  1. Go to cell C2 and apply below concatenate code.


    date to text example 2-1

    First, up it will show the value as “Shwetha Menon’s Birthday is on 37604″. It does not make any sense to read it because the formula is showing the date as numbers only. Therefore, we need to format the number and apply a date format to it.

  2. Apply the TEXT function to get the correct format. In cell C2, apply the below formula.


    date to text example 2-2

    Result: 

    date to text example 2-3

    Note: Apply different format styles shown in the early table to get different results and understand.

Example #3 – Convert Date to Text Using Text to Column Option

If you do not like formulas in Excel to convert the date to text format, you can use TEXT TO COLUMN OPTION. Assume you have data from cells A2 to A8.

date to text example 3

Now you need to convert it into text format.

Step 1: Select the entire column you want to convert.

date to text example 3-1

Step 2: Go to Data > Text to Columns in Excel.

date to text example 3-2

Step 3: Make sure delimiter is selected and click on the Next button.

Step 4: Now, the below pop up will open and uncheck all the boxes and click the Next button.

date to text example 3-3

Step 5: Select the TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish.

date to text example 3-4

Step 6: Now, it instantly converts it into text format.

date to text example 3-5

Example #4 – Convert Date to Text in Excel using Formula

Use the Formula Method to convert the number to a Date format. Assume you have the below data in your excel file.

date to text example 4

Formulas you need to know to convert them to YYYY-MM-DD are DATE, LEFT, RIGHT & MID functions. Moreover, the formula is.

Date: Date Function in ExcelDate Function In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more formats it into Year-Month-Day format.

date to text example 4-1

Left: LEFT Function in ExcelLEFT Function In ExcelThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( "ANAND",2), the result will be AN.read more will take the first portion for year format. Takes 4 first 4 characters as Year format.

date to text example 4-2

Mid: MID Function will take the middle part of the data for Month format. Takes middle 2 characters for Month format.

date to text example 4-3

Right: RIGHT Function in ExcelRIGHT Function In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more will take the last part for Day format. Takes the last 2 characters for Day format.

date to text example 4-4

Now, go ahead and apply the formula to get the date format.

date to text example 4-5

Now, let us elaborate on each part.

Part 1: LEFT (B2, 4) this means, in cell B2, take the first four characters. i.e., 2017

Part 2: MID (B2, 5, 2) this means, in cell B2, starting from 5th character, select two characters. i.e., 08.

Part 3: RIGHT (B2, 2) this means, in cell B2 from the right side, select two characters. i.e., 15

Now, the Date function will combine all these and give you the value as below. Drag & drop the formula for remaining cells.

date to text example 4-6

Recommended Articles

This has been a guide to Convert Date to Text in Excel. Here we discuss how to convert date to text in excel using three methods – 1) Text Function and 2) Text to Column Option 3) Formula Method along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>