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. 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 the 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 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. , 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.
- 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.
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.
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.
- 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.
- Apply the TEXT function to get the correct format. In cell C2, apply the below formula.
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.
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 the Next button.
Step 4: Now, the below pop up will open and uncheck all the boxes and click the Next button.
Step 5: Select the TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish.
Step 6: Now, it instantly converts it into 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 the 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 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. 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 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.
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 –