WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Date to Text in Excel

Date to Text in Excel

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to Convert Date to Text in Excel?

The conversion of the date to text is very easy and simple. There are four simple ways to convert date to text in Excel:

  1. Using the Text Function (most commonly used)
  2. Using the Text to Column option
  3. Using the Copy-Paste method
  4. Using VBA

Use of Date to Text in Worksheet

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 TEXT formula in excel is as follows:

Text Formula in Excel

  • 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:

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • day format
  • month format
  • year format
  • separator
You can download this Convert Date to Text Excel Template here – Convert Date to Text Excel Template

#1 Using Text Function

Example #1

Apply the formula =A4&”‘s joining date is “&TEXT(B4,”dd-mm-yyyy”)

Date to Text in Excel Example 1

To get the below output.

Date to Text in Excel Example 1-1

Example #2

You can also convert the current date to text by below formula

=”Today is “&TEXT(TODAY(),”dd/mm/yyyy”)

Date to Text in Excel Example 2

To get the below output.

Date to Text in Excel Example 2-1

#2 Using Text to Column

Suppose you have a dataset as shown in the below, and you want to convert these dates into text format:

Date to Text in Excel Example 3

Select the data and Go to Data, then Data Tools and Text to Column in Excel.

date to text in excel example 3-1

Press Next and uncheck all Delimiters

date to text in excel example 3-2

Select the location and click on the finish, as shown in the below figure.

date to text in excel example 3-3

The output will be as follows:

date to text in excel example 3-4

#3 Using Copy Paste Method

Consider the data set of joining date

date to text in excel example 4

  • Copy the data. 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

date to text in excel example 4-1

date to text in excel example 4-2

#4 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”)

Next c

End Sub

Things to Remember

  • 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.

Recommended Articles

This has been a guide on how to convert Date to Text in Excel. Here we discuss the top 3 methods to convert the Date to Text: 1) Text Formula, 2) Text to Column, and 3) Copy Paste Method along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • How to add Leading Zeros in Excel?
  • Char Function In Excel
  • CODE Excel Function
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Convert Date to Text Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More