Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Table of Contents
Convert Date to Text in Excel
Generally, Date in Excel is stored as numbers. This allows a user to use these dates in calculations in excel. For example, you can add any number of days to a given date.
However, sometimes the date is required as text. You need to convert the date into the text format.
How to Convert Date to Text in Excel?
The conversion of the date to text is very easy and simple.
There are three simple ways to convert date to text in Excel:
- Using the Text Function (most commonly used)
- Using the Text to Column option
- Using Copy Paste method
Use of Date to Text in Excel worksheet.
In Excel Text function is commonly used to convert a numeric value to a text string and display it in the format you specify so we can use it to convert date to text in excel format.
Date to Text Function in Excel
The formula of the Excel TEXT function is as follows:
Value: It is a numeric value you want to convert to text. This can be a number, a formula that returns a numeric value, or a reference to a cell containing a number.
format_text: This is how you want to format the resulting text value, provided as a text string enclosed in quotation marks.
Using the Text function to convert date to text in excel requires a basic understanding of the formats that you can use in it.
In the case of dates, there are four parts to the format:
- day format
- month format
- year format
Here are the formats you can use for each part:
- d – for day number.
- dd –For day number with a leading zero.
- ddd – For Wednesday, it will show Wed.
- dddd –For Tuesday, it will be shown as Tuesday.
- m –for month number
- mm – for month number with a leading zero
- mmm –For August, it will show Aug.
- mmmm For August, it will show complete August.
- yy –For 2018, it will show 18.
- yyyy –For 2018, it will show 2018.
- / (forward slash): A forward slash can be used to separate the day, month, and year part of a date. For example, if you specify “dd/mmm/yyyy” as the format, it would return a date with the following format: 31/12/2016.
- – (dash): A dash can be used to separate the day, month, and year part of a date. For example, if you specify “dd-mmm-yyyy” as the format, it would return a date with the following format: 31-11-2018.
- Space and comma: You can also combine space and comma to create a format such as “dd mmm, yyyy”. This would show the date in the following format: 31 Jul, 2018.
Example #1 – Text Function in Excel
Let’s again take the example of the date of joining:
Apply the formula =A4&”‘s joining date is “&TEXT(B4,”dd-mm-yyyy”)
To get the below output.
Example #2 – Covert Date using Text Function
You can also convert the current date to text by below formula
=”Today is “&TEXT(TODAY(),”dd/mm/yyyy”)
To get the below output.
Example #3 – Convert Date to Text in Excel using Text to Column
Suppose you have a dataset as shown in the below and you want to convert these dates into text format:
Select the data and Go to Data then Data Tools and Text to Column.
Press Next and uncheck all Delimiters
Select the location and click on the finish as shown in the below figure.
The output will be as follows:
Example #4 – Convert Date to Text using the Copy-Paste Method
Consider the data set of joining date
Copy it and paste it in the notepad Open a Notepad and paste it there. As soon as you paste the dates in the notepad, it automatically gets converted into text.
Now switch back to the Excel and select the cells where you want to paste these dates.
With the cells selected, go to Home then Number and select the Text format (from the drop down).
Paste the date as text
Convert Date to Text using VBA.
Suppose we have the text in the Sheet1 and in the H2 cell then apply the Date to text by using the below VBA code.
Sub Datetotexts ()
Dim c As Range
For Each c In Selection
c.Value = Format(c.Value, “dd.mm.yyyy”)
Things to Remember about the Date to Text in Excel:
- Left-aligned by default
- If several text dates are selected, the Status Bar only shows Count.
- There may be a leading apostrophe visible in the formula bar.
This has been a guide on how to convert Date to Text in Excel. Here we discuss top 3 methods to convert the Date to Text in Excel – 1) Text Formula 2) Text to Column and 3) Copy Paste Method along with excel example and downloadable templates. You may also look at these useful functions in excel –