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.
- 1. Convert date to text
1.1 Convert date to text in mm/dd/yyyy format
1.2 Convert date to text in mmddyyyy format
1.3 Convert date to text in other formats - 2. Convert date to number
2.1 Convert date to number in 5-digit format
2.2 Convert date to number in mmddyyyy format - 3. Convert date to month/day/year or other date formats with clicks
- 4. Download sample file
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
If you want to convert date in Cell A8 to dd/mm/yyyy, use this formula
=TEXT(A8,"dd/mm/yyyy")
press Enter key
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
If you want to convert date in Cell A18 to text in ddmmyyyy format, type this formula
=TEXT(A18,"ddmmyyyy")
press Enter key
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 |
 |
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. |
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.
2. In Format Cells dialog，under Number tab， select General from the pane of Category.
3. Click OK. The date in selected cell has been converted to number string in mmddyyyy format.
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.
3. Click OK. The date in selected cell has been converted to number string in mmddyyyy format
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 |
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.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.
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.
4. Click Ok, then dates you selected have been converted to the date format you selected.
