Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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!

Now, Let These Advanced Date Tools Boost Your Productivity, Won The Opportunity On Pay Rise And Promotion

16 date tools in Kutools for Excel solve 90% tasks about date processing in Excel for you, which covers date addition, datetime conversion and so on.

◆ One-click tools handle batch processing, decrease thousands of mouse-clicks for you, avoid mouse hand.

◆ Total 300 tools in Kutools for Excel help you quickly handle 1500 Excel scenarios.

◆ Seconds handle any complicated problems, improve 70% efficiency, make your life and work easier than you’ve ever been.

◆ 110000+ high efficiency people and 300+ world renowned companies’ choice.

◆ 60-day free trial with full feactures, 60-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 70% 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.

60-day free trial with full features, 60-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


How To Make You Stand Out From Crowd, Easily Work And Leisurely Enjoy Life?

Kutools for Excel will averagely improve your 70% efficiency every day, make you have much time to accompany with family and have fun in life.

300 advanced tools solve 80% Excel problems, you no longer struggle to search solutions, more easily complete work.

Easily handle 1500 Excel scenarios, save at least 1 hour for you every day.

Become an Excel expert in 5 minutes, gain peoples’ recognition and opportunity for promotion.

One-click utilities handle batch processing, let you avoid thousands of mouse-clicks, say good-bye to mouse hand.

Get rid of rote memorizing formulas and complex VBA, give a rest to your brain.

110000+ high efficiency officers and 300+ world renowned companies’ choice.

Only take $39 but much worth than $4000 training of other people

60 days free trial without any limitation, full money back in 60-day without any reasons.


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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.