Blogger Text

My Aim to Provide you quality contents, Tips & Tricks, Software, Microsoft Office, Graphic Editing (Adobe PhotoShop, After Affects, Illustrator, inDesign) Corel Draw, Corel Video Studio, Cyberlink PowerDirector, Power ActionCinema, Tutorials about Blogging and VU Assignments, Quizes & GDB Solutions and Much More... at regular Basis
                                     ***    Kindly Subscribe our Official YouTube Channel "INFOPALACESS OFFICIAL-Tuts: in this channel we upload Programming (C,C++,C# JAVA, PHP), Web Development, Graphics Editing and Microsoft Office Step by Step Tutorials from bigginer to Advance Level. We also provide free online courses at our YouTube Channel. ***   Graded Assignments/Quizes and GDB will start in Next Week. Solution ideas of All assignments, Quizes and GDB will be available here. If you have any problem regarding this then you can contact us.

Covert Date to Text in Excel

Convert Date to Text

Convert date to Text/String

If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like "dd/mm/yyyy", "yyyy-mm-dd", etc. to convert a valid date to a text value. See table below for a list of available tokens.
There are many uses of Text Formula and most of the useful is formating Numbers. for detail Click Here but here we only discuss about the formating of date in to any text/string as per our need.

Text Formula



Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000 is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:

=TEXT(A1,"mmm")              // "Jan"
=TEXT(A1,"dd/mm/yyyy")     // "08/11/2022"
=TEXT(A1,"dd-mmm-yy")     // "08-Nov-22"
Excel TextFunction Pic

Dates Format Codes

Assuming a date of November 08, 2022, here is a more complete set of formatting codes for date, along with sample output.

Format Code Output Detail
d 8 day display in single number only
dd 08 day display in 2 numbers
ddd Tue three-letter abbreviation of the day
dddd Tuesday Full Name of Day display
m 1 Month Display in Single Digit
mm 01 Month Display in Digit with leading Zero
mmm Nov three-letter abbreviation of the month
mmmm November Full name of the month in alphabtics
yy 22 two-digit year
yyyy 2022 four-digit year
h 1 Single Digit Hour
hh 01 two Digit Hour
M 1 Single-digit minutes
MM 01 Two-digit minutes with leading zero
s 1 Single-digit Seconds
ss 01 Two-digit seconds with leading zero
AM/PM PM Showing Time in 12 hours Format


mm/dd/yyyy 11/07/2022 -
mm-dd-yyyy 11-07-2022 -
ddd, mmm d Tue, Nov, 8 -
dd/mm/yyyy hh:mm:ss 09/01/2022 17:15:00 date with time
09/01/2022 17:15:00 01/09/2022 5:15 PM Date and Time in 12 hours format

As I mentioned above that you can use the TEXT function to convert dates or any numeric value to a fixed text format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left.

Source:    ExcelJet

Post a Comment