Convert Date to Text using Excel (Table of Contents)
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 excel 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.
Right click on the cell and select Format Cells option.
Select the date option and select the below option.
Now, the result will be as below.
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 Excel, we need to use TEXT function in excel. As I already mentioned time & date in Excel are stored as numbers. However, sometimes we may require showing it as the text string. In such cases, we can use TEXT function.
- 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. Below table will give a better idea about the different format and results.
Example #1 – Convert Date to Text in Excel using “TEXT” Function
From cell A2 to A10, we have below values and convert them to Date from B2 to B10.
In order to convert them to date format, in cell B2 write the below formula.
Press enter and drag the formula
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.
Step 1: Go to cell C2 and apply below concatenate code.
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.
Step 2: Apply TEXT function to get the correct format. In the cell, C2 apply the below formula.
Note: Apply different format style shown in the early table to get the 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.
Now you need to convert it into text format.
Step 1: Select the entire column you want to convert.
Step 2: Go to Data > Text to Columns in Excel
Step 3: Make sure delimiter is selected and click on next button.
Step 4: Now, the below pop up will open and uncheck all the boxes and click Next button.
Step 5: Select TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish..
Step 6: Now it instantly convert it in to text format.
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 below data in your excel file.
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 Excel formats it into Year-Month-Day format.
Left: LEFT Function in Excel will take the first portion for year format. Takes 4 first 4 characters as Year format.
Mid: MID Function will take the middle part of the data for Month format. Takes middle 2 characters for Month format.
Right: RIGHT Function in Excel will take the last part for Day format. Takes the last 2 characters for Day format.
Now, go ahead and apply the formula to get the date format.
Now, let us elaborate on each part.
Part 1: LEFT (B2, 4) this means, in cell B2 take the first 4 characters. i.e. 2017
Part 2: MID (B2, 5, 2) this means, in cell B2 starting from 5th character select 2 characters. i.e. 08.
Part 3: RIGHT (B2, 2) this means, in cell B2 from right side select 2 characters. i.e. 15
Now, Date function will combine all these and give you the value as below. Drag & drop the formula for remaining cells.
This has been a guide to Convert Date to Text in Excel. Here we discuss how to convert date to text in excel using 3 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 –