Excel Formula: Convert Date to Text
Author: Sun
Last Modified: 2020-09-07
This tutorial provides the TEXT function to convert a date to text.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
TEXT(date,format) |
Arguments
Date: the date you want to convert to text. |
Format: the format you want the date displayed as. |
Return Value
The TEXT function returns to a text value.
How this formula work
To convert the date in cell B3 to a text value, please use below formula:
=TEXT(B3,"mm/dd/yyyy") |
Press Enter key to get the result.
Explanation
TEXT function: convert a number to a text in a specific format.
Note
Format Code | Formula | Result |
yyyy | =TEXT(B3,"yyyy") | 2020 |
yy | =TEXT(B3,"yy") | 20 |
mmmmm | =TEXT(B3,"mmmmm") | S |
mmmm | =TEXT(B3,"mmmm") | September |
mmm | =TEXT(B3,"mmm") | Sep |
mm | =TEXT(B3,"mm") | 09 |
m | =TEXT(B3,"m") | 9 |
dddd | =TEXT(B3,"dddd") | Saturday |
ddd | =TEXT(B3,"ddd") | Sat |
dd | =TEXT(B3,"dd") | 12 |
d | =TEXT(B3,"d") | 12 |
mm/dd/yyyy | =TEXT(B3,"mm/dd/yyyy") | 09/12/2020 |
m/d/y | =TEXT(B3,"m/d/y") | 9/12/20 |
ddd,mmm y | =TEXT(B3,"ddd,mmm y") | Sat,Sep 20 |
Relative Formulas
- Convert date to Julian
Sometimes, you need to convert the standard date to the Julian date in Excel, here this article introduces a formula which combines the TEXT, YEAR and DATE functions to solve this job. - Convert date string to datetime
Sometimes, while you import or paste some datetime data from other devices to Excel, the datetimes maybe not be recognized as proper dates... - Convert date to year month
In some cases, you want to convert the date to only month, year, day or monthyear, daymonth, or year day in text format. - Calculate Hours Minutes Seconds Between Two Times
Here in this tutorial, it introduces some formulas to quickly present the time difference result as single time unit.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Sort comments by
#42557
This comment was minimized by the moderator on the site
0
0
#42653
This comment was minimized by the moderator on the site
Report
0
0
#42654
This comment was minimized by the moderator on the site
0
0
#42891
This comment was minimized by the moderator on the site
Report
0
0
#42958
This comment was minimized by the moderator on the site
0
0
#43089
This comment was minimized by the moderator on the site
Report
0
0
There are no comments posted here yet