Convert Date to Text in Excel is a versatile function that offers flexibility in formatting and compatibility. By converting dates to text, users can ensure consistency in their data and avoid formatting issues when sharing files across different platforms or software. Whether it’s for reporting, analysis, or data presentation, mastering this function is essential for Excel users. With Convert Date to Text in Excel, you can effortlessly handle date-related tasks and streamline your workflow, making it an indispensable tool in your arsenal.
- How to Convert a Specified Date to Text?
- How to Convert Current Date to Text?
- How to Convert Date to Text using Text to Column?
- How to Convert an 8-digit Date to an Excel-Recognizable Date?
- Why are Dates Stored as Numbers?
- How to get Day Name as a Text from a Date?
1. How to Convert a Specified Date to Text using TEXT Function in Excel
Step 1: First, take the example of HR department joining date, enter the Person’s name and Date into the Excel spreadsheet to get an organized table of data as shown in the image.
Let’s take the below table.
Step 2: Add the column in from C2:C8 to get the converting Date to text string.
Step 3: Suppose, you want to Covert the Date of B2 and the person’s name is Waseem. For converting Date to text value there you need to use a formula.
The formula is: =A3&”‘s joining date is “&TEXT(B3,”dd-mm-yyyy”)
Applied the formula here.
Step 4: Below you can see the converting result.
Step 5: Use the same Excel formulas from Step 3 but according to the range and get all output there.
For, Sunny the formula will be: =A4&”‘s joining date is “&TEXT(B4,”dd-mm-yyyy”)
For, Aiman the formula will be: =A5&”‘s joining date is “&TEXT(B5,”dd-mm-yyyy”)
For, Sayor the formula will be: =A6&”‘s joining date is “&TEXT(B6,”dd-mm-yyyy”)
For, Roni the formula will be: =A7&”‘s joining date is “&TEXT(B7,”dd-mm-yyyy”)
For, Sakib the formula will be: =A8&”‘s joining date is “&TEXT(B8,”dd-mm-yyyy”)
Here are all the persons’ joining dates converted to text.
2. How to Convert the Current Date to Text by Excel TEXT function?
Step 1: By using the TEXT function in Excel, you can change your current date to text.
You can use the TEXT function to specify a suitable date format for conversion. Make a table and write down the current date there.
The date is entered below.
Step 2: Add the column in from B2:B3 to get the current date to text there.
The column has been added you can see it below.
Step 3: Write the formula. The formula is: =”Today is “&TEXT(TODAY(),”dd/mm/yyyy”)
Used the formula here.
Step 4: Click the enter button then get the result of the Current date as text.
Here is the result below.
3. How to Convert a Date to Text in Excel using Text to Column Feature?
Step 1: First, you need to put some dates into excel to convert them into text using the text column.
Make a dataset as shown below.
Step 2: Now you want to Convert Date to Text using Text to Column to get this add the column from to get the result there.
Added another column here.
Step 3: To change the dates into text format, you must choose all the cells in the data set that contains dates. Then, Select Data -> Data Tools => Text in Columns.
Step 4: After clicking on the Text to Column option you will get a box Convert Text to Column Wizard- Step 1 of 3, Make sure Delimited is selected and click Next in Delimiter Options as shown below.
Step 5: After clicking on the Next option you will get Convert Text to Column Wizard- Step 2 of 3 , select the one that is selected by default and click Next as instructed below.
Step 6: Now, you will get Convert Text to Column Wizard- Step 3 of 3 page, Select Date from the ‘Format Column Data’ option and specify the destination cell (in this example B3) then click Finish. This will immediately convert the date to text format.
Step 7: After clicking on Finish button, you will get the output.
Here, is the output.
4. How to Convert an 8-digit Date to an Excel-Recognizable Date?
Step 1: You have to make a dataset and put that data into the excel sheet.
Placed the information here.
Step 2: Add the column in B1:B5 to get the output there.
The column has been added you can see below.
Step 3: Write the formula and according to the column range. The formula will be: =DATE(LEFT(A2, 4), MID(A2, 5, 2), RIGHT(A2, 2))
Applied the formula here.
Step 4: Click the enter button then get the date.
Here is the result below.
Step 5: Use the same formula from Step 3 but according to the column range or drag down the excel cursor from B2:B5 and get all output there.
5. Why are Excel Dates Stored as Numbers?
Various functions in Excel can be used to alter dates within a worksheet, such as the following: DAYS, DATE, WORKDAY, and Date VALUE. The list is extensive. They need a standardized format for these formulas to be recognized as ‘a date. Verify that you are utilizing the DAYS feature and have allocated two days. Using your input, Excel calculates the number of days to produce the result.
How many days elapsed between these dates?
Unless there is a serial number available, Excel must resort to another approach for this computation. Using Excel’s serial number assignment method, the number of days between two serial numbers will be calculated by subtracting them.
6. How to get Day Name as a Text from a Date by Formula?
Step 1: First, you need to enter their data into an Excel spreadsheet to get an organized table of data as shown in the image.
Placed the data here.
Step 2: Now you want to find out the Day name so for this add another column from B1:B6.
Added another column here.
Step 3: Use the formula now. The formula is: =MINIFS(B2:B7,A2:A7,”Mango Juice”)
Applied the formula here.
Step 4: Here you can see, that the Day is Sunday.
The result is outlined below.
Step 5: Use the same formula from Step 3 but according to the column range or drag down the excel cursor from B2:B6 and get all output there.
The result is outlined.
Application of Excel Convert Date to Text – Excel Tips
- Date Formatting: Convert dates to text to customize their appearance in reports or documents.
- Compatibility: Ensures compatibility when exporting Excel data to other software that doesn’t recognize date formats.
- Text Functions: Enables the use of text manipulation functions on dates, such as concatenation or substring extraction.
- Data Validation: Facilitates data validation rules that require text input rather than date values.
- Custom Labels: Useful for labeling charts or tables where specific text labels are required instead of date values.
- Data Import: Helps in scenarios where text-based systems require date information in a particular format cells.
For ready-to-use Dashboard Templates:
