Note: The other languages of the website are Google-translated. Back to English

How to convert date to number string or text format in Excel?

In this tutorial, I will introduce several methods on solving different cases about converting date to number or text format in Excel.
doc date to number text 1


1. Convert date to text

This section provides the methods on converting date to text in Excel.

To convert date to text, you just need one formula.

Formula: =TEXT(date,”date_format”)

Reference: date: the cell with date you want to convert to text
date_format: the format you want to display after converting date to text

In below cases, I will tell you how to use this formula.

1.1 Convert date to text in mm/dd/yyyy format

For example, convert date in Cell A3 to mm/dd/yyyy, use the formula
=TEXT(A3,"mm/dd/yyyy")
press Enter key
doc date to number text 2

If you want to convert date in Cell A8 to dd/mm/yyyy, use this formula
=TEXT(A8,"dd/mm/yyyy")
press Enter key
doc date to number text 3

1.2 Convert date to text in mmddyyyy or ddmmyyyy format

If you want to convert date in Cell A13 to text, but in mmddyyyy format, type this formula
=TEXT(A13,"mmddyyyy")
press Enter key
doc date to number text 4

If you want to convert date in Cell A18 to text in ddmmyyyy format, type this formula
=TEXT(A18,"ddmmyyyy")
press Enter key
doc date to number text 5

1.3 Convert date to text in other formats

Actually, whatever text format you want to convert date to, just type the format you want between quotation marks in formula.

Date(A3) 12/23/2019
Formula =TEXT(A3,"mm-dd-yyyy") =TEXT(A3,"mm/dd") =TEXT(A3,"dd") =TEXT(A3,"d/m/y")
Result 23-12-2019 23/12 23 23/12/19

New!
date tools

16 new date functions and features solve 90% tasks about date processing in Excel for you.

◆ Add or subtract year/months/days/weeks to date, click for details.

◆ Add or subtract seconds/minutes/hours to date, click for more.

◆ Count weekends/weekdays between two dates. click for more.

◆ Remove time from date. click for details.

◆ Convert time to decimal seconds/munites/hours. click for details.

◆ Convert multiple date formats to US standard date format. click for details.

30-day free trial with full feactures, 30-day no reason refund.


2. Convert date to number

In this section, I provide methods on converting date to number in 5-digit format of mmddyyyy format.

2.1 Convert date to number in 5-digit format

If you want to convert date to number in 5-digit format, just do as these:

1. Right click at the cell which contains the date you want to convert to number, and in the right-click menu, select Format Cells common.
doc date to number text 6

2. In Format Cells dialog,under Number tab, select General from the pane of Category.
doc date to number text 7

3. Click OK. The date in selected cell has been converted to number string in mmddyyyy format.
doc date to number text 8

2.2 Convert date to number in mmddyyyy or ddmmyyyy format

If you want to convert date to number string in mmddyyyy or ddmmyyyy format, you also can apply the Format Cells function.

1. Right click at the cell which contains the date you want to convert to number, and in the right-click menu, select Format Cells common.

2. In the Format Cells dialog, under Number tab, select Custom from the Category pane, then go to right section, enter mmddyyyy into the Type textbox.
doc date to number text 9

3. Click OK. The date in selected cell has been converted to number string in mmddyyyy format
doc date to number text 10

If you want to convert date to number string in other format, you can take below list as reference.

Date 2/23/2019
Format Cell as custom ddmmyyyy ddmmyy mmyyyy yyyymmdd
Display 23022019 230219 022019 20190223

3. Convert date to month/day/year or other date formats with clicks

If you want to convert date to month, day, year or other date formats quickly, the Apply Date Formatting utility of Kutools for Excel will be a good choice to use.

Say Goodbye To Mouse Hand and Cervical Spondylosis Now

300 advanced tools of Kutools for Excel solve 80% Excel tasks in seconds, pull you out of the thousands of mouse-clicks.

Easily deal with 1500 working scenarios, no need to waste time for searching solutions, have much time to enjoy your life.

Improve 80% productivity for 110000+ highly effective people every day, of course including you.

No longer to be tormented by painful formulas and VBA, give your brain a rest and joyful working mood.

30-day free trial with full features, 30-day money back without reasons.

A Better Body Creates A Better Life.

After free installing Kutools for Excel, please do as below:

1. Select the cells which contains the dates you want to convert to other date formats.

2. Click Kutools > Format > Apply Date Formatting.
doc date to number text 11

3. In the Apply Date Formatting dialog, select the date format you want to use in Date formatting pane, at the meanwhile, the converted result will be previewed in right Preview pane.
doc date to number text 12

4. Click Ok, then dates you selected have been converted to the date format you selected.
doc date to number text 13

Convert date to multiple Individuality date formatting with clicks

apply date formatting

More tips on date conversion


Download sample file

sample


Recommended Productivity Tools

Office Tab - Tabbed Browsing, Editing, Managing Of Documents In Microsoft Office 2019 - 2003 And Office 365


office tab

Kutools for Excel - Combines More Than 300 Advanced Functions and Tools for Microsoft Excel


kutools tab
kutoolsp tab
Comments (36)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hey I need to find an equation that lets me change the date to a decimal equation used in normal book work Eg Original Date 25/9/2003 Starting point date on graph = 2000 Date= 3+[(31+28+31+30+31+30+31+25)/365] = 3.74
This comment was minimized by the moderator on the site
Today is 7/1/2020. My formula is ="JENN"&TODAY()

I get JENN44013

Help
This comment was minimized by the moderator on the site
want to convert 15/08/2015 into word (Fifteenth August Two Thousand Fifteen) format by using function in excel 2007
This comment was minimized by the moderator on the site
Hello, thanks for your leaving message. To convert date to word, here is a VBA code can help you, however, it is too long to splace here. You can go to this article to get the VBA. https://www.extendoffice.com/documents/excel/4694-excel-date-to-words.html
Hope it does favor.
This comment was minimized by the moderator on the site
Hi,
I'm trying to convert dates formated as yyyy-mm-dd (2018-01-06) and when entering this function, it displays yyyy-01-dd. I can't make sense of it, can anyone help?
This comment was minimized by the moderator on the site
You can try to format the date cells as custom, and type yyyy-mm-dd into the Type textbox in Format Cells dialog. If you want to know more detail, this is an article talk about three ways to convert dates to yyyy-mm-dd. https://www.extendoffice.com/documents/excel/3289-excel-convert-date-to-yyyy-mm-dd-format.html
This comment was minimized by the moderator on the site
This happened to me as well. I kept getting yyyy-00-dd. It's because I'm in a different country and had to use the local format. =TEXT(A1;"vvvv-kk-pp") worked fine for me once I figured that out. If you right-click: Format Cells: Custom, you should find examples that use the local year, month, and day formats.
This comment was minimized by the moderator on the site
Hi,

I want to convert a date like 23/05/2018 to 23052018. Any formula to do that automatically?

Thank you
This comment was minimized by the moderator on the site
Just use Find and Replace function, in findwhat textbox, type /, leave blank in replacewith textbox, to remove all / from the dates.
This comment was minimized by the moderator on the site
=SUBSTITUTE(A1,"/","")
This comment was minimized by the moderator on the site
Hello, I want to convert 1-Apr-20 to 20200401, how can i do it automatically? Thank you.
This comment was minimized by the moderator on the site
Hello,Kristina, you just need to format the cells to yyyymmdd in Format cells dialog, more detail please go to https://www.extendoffice.com/documents/excel/4165-excel-convert-mm-dd-yyyy-to-yyyymmdd.html
This comment was minimized by the moderator on the site
I am using a date 12 September 2018 I then convert it to 091218. I would like to use a concatenate function to add a 01 at the end, however when I do this the value keeps going to 43355. Is there any easy way to do this or is this even possible?? thanks in advance
This comment was minimized by the moderator on the site
Sorry, Jim, I do not understand your problem, could you upload a screenshot to describe your question?
This comment was minimized by the moderator on the site
hello can you help me?

i want to convert 20180107 to 07/01/2018
This comment was minimized by the moderator on the site
Hello, mat, you can use this formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
This comment was minimized by the moderator on the site
I don't understood!!
This comment was minimized by the moderator on the site
These numbers 1532307209286 were converted by my partner to date 23-07-2018 00:53:29. Do you know how did it?
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi, Isma, your number is unix timestamp, right? If so, this article can help you. https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html#a3
This comment was minimized by the moderator on the site
1437620400 how can I convert this number in to a date in excel?
This comment was minimized by the moderator on the site
Hi, gmsaez, is the number timestamp? If so, please use this formula =(((A1/60)/60)/24)+DATE(1970,1,1), A1 is the number, then format the result as date format. For more details, you can refer to this article https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html#a3
This comment was minimized by the moderator on the site
How do I do an IF formula to test which date is older i.e. auto date is 8/1/2019... a different cell is manual entry 8/3/2019. I want to check to confirm the entered date is not older than the auto populated date. something like =IF(((DATEVALUE(A2)>(DATEVALUE(G3)),1,0) DATE VALUE seems to be not considered a number so it fails with a value error.
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
There is another way, it returns exactly the numeric format that represents the date abbreviated by (/), it would be: '=Substitute(A1;"/";"")
This comment was minimized by the moderator on the site
Hi i have a date in "13/02/2020 11:42:09" format and i want in MM/DD/YYYY format
This comment was minimized by the moderator on the site
Hi, just select the cell then apply the Text to Columns, in the Text to Columns, choose Delimiter > Space > Date(in the Date format, choose DMY) > Finish.
This comment was minimized by the moderator on the site
Hi, I want to convert month to number. For exp: 8 months (date format) ---> "8" as a number or value. I've change its format but I can't count it.Because I want to put it to math format, for exp: 8-2 = 6 (8 from the months). But the 8 doesn't work.Hope you get it. Thank you for helping!
This comment was minimized by the moderator on the site
Hello. I have the date 26.01.2021 in the cell A1 and in another cell I have the formula: =RIGHT(A1,4) to take the year from that date, and it returns “4526” instead of “2021”. Do you know how to solve it? Thank you very much!
This comment was minimized by the moderator on the site
try to change the format on cell A1 to general or number and then enter the values that you want again to see if it helps. I think your problem is the format issue.
This comment was minimized by the moderator on the site
I want to convert the day (Tuesday) which is showing up as 1/3/1900 when you click on it because I added a formula on the original data to provide me with Day values. Now I want to use a pivot table to show me the actual days. How do I convert the 1/3/1900 which shows up at Tuesday to just Tuesday? 
This comment was minimized by the moderator on the site
Hi, Cathey, do you want to convert the formula to an actual value? If so, you can use the Paste as Value Only feature in Excel to copy and paste the formulas as values only, or you can use Kutools for Excel's To Actual feature to convert formulas to values.
This comment was minimized by the moderator on the site
Hi, I was trying to copy and past the value but its still showing the date as opposed to the day. 
This comment was minimized by the moderator on the site
Hi, Cathey, do you choose the Paste as Value option in right-click context menu? If you have choosed, try to press Ctrl +1 keys to enable format cells feature and change the cell format to mm/dd/yyyy in Date section.
This comment was minimized by the moderator on the site
Hello. I would very much appreciate your help with this formula. I am wishing to enter the customer's date of birth in cell I3 (custom formatting mm/dd/yy) and would like that date of birth to automatically populate in A3 cell as mmddyy along with adding the First letter from First name cell F3 and First letter from Last name cell G3 in order to have it auto-populate an ID# for our customers in our database.

A3 F3 G3 I3
ID# First Name Last Name Date of Birth
030199AS Adam Smith 03/01/1999

Thank you so much for any help. You have no idea how much I appreciate it. I have been wracking my brain all night to no avail. :(
Shena Bruno
This comment was minimized by the moderator on the site
Hi, Shena, Bruno, in A3, please use the formula =TEXT(I3, mmddyyyy")&LEFT(F3,1)&LEFT(G3,1).
In the formula, it can be explained as two parts:
TEXT(I3, mmddyyyy") will change the value in I3 to this format mmddyyyy
LEFT(F3,1)&LEFT(G3,1) will extract the first character from left side of the strings in cell F3 and G3.
Hope it is helpful.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations