How to convert date to number string or text format in Excel?
Converting date values in Excel to either text or number formats is a common need during data processing and reporting. For example, you may have imported data where dates are stored as numbers or you want to standardize date formats before exporting to external systems, generating CSV files, or performing batch data calculations. In this tutorial, several practical methods are introduced, covering different conversion scenarios and requirements, so you can flexibly choose the most suitable approach according to your actual work.
The effectiveness, simplicity, and batch handling capabilities of each method are analyzed, along with key tips, precautions, and troubleshooting suggestions. The methods include formulas, formatting tools, Excel built-in features, as well as VBA automation, which will greatly facilitate your date conversion tasks 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 in5-digit format
2.2 Convert date to number in mmddyyyy format - 3. Convert date to month/day/year or other date formats with clicks4
- 4. Text to Columns: Convert date values
- 5. Download sample file
This section provides essential techniques for converting dates to text strings in Excel.
The easiest way to convert a date to text is by using the TEXT function, which can flexibly specify the output format based on your needs and regional settings. This method is suitable for converting any date format into a readable text string and is widely used for exporting data, preparing summaries, or quickly standardizing date displays.
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
Below, specific subcases demonstrate how to use this formula effectively for different display requirements. Please note that after using the TEXT formula, the output is always returned as a text string (not a number), so it is suitable for cases where you do not need to perform further date calculations.
1.1 Convert date to text in mm/dd/yyyy format
To convert the date in Cell A3 to a string in mm/dd/yyyy format, enter the following formula into your target cell (such as B3):
=TEXT(A3,"mm/dd/yyyy")
Press Enter to confirm the formula.
If you prefer the dd/mm/yyyy format, you can adjust the formula to:
=TEXT(A8,"dd/mm/yyyy")
After typing the formula, press Enter.
You can drag the fill handle to apply the formula to other cells if you need to convert multiple dates at once. Remember, the result is a text string—Excel aligns it to the left by default. If you intend to use the result for calculations, refer to the number conversion methods below.
1.2 Convert date to text in mmddyyyy or ddmmyyyy format
If you want to output dates in a compact format (such as mmddyyyy), the same approach applies:
Enter this formula in the target cell (e.g., B13):
=TEXT(A13,"mmddyyyy")
Press Enter to finish.
For the ddmmyyyy format, type:
=TEXT(A18,"ddmmyyyy")
Then press Enter.
If you need to apply the operation to a column, you can copy the formula down or use the fill handle. Should you encounter alignment or calculation errors, ensure that all original date cells are truly date values and not already formatted as text.
1.3 Convert date to text in other formats
The TEXT function supports a wide variety of formats. Simply change the format code in the formula to customize the output. This is useful for preparing different region-specific date strings, for document generation, or for use in other software that requires fixed date formats. Examples:
| 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 |
If the formula does not return the desired format, double-check your format string—Excel requires double quotation marks and valid date codes. For a comprehensive list of formatting options, consult Excel’s Custom Format help.
This section explores how to convert date values into number strings. In some cases, you may want to get the date serial number (for interval calculations or compatibility purposes), or to reformat dates as pure number strings (such as for database exports, sorting, or when feeding structured numerical data into another application).
2.1 Convert date to number in5-digit format
To display the underlying serial number (the number of days since January 1,1900) corresponding to a date, follow these steps:
1. Right-click the cell containing the date you want to convert, then in the context menu, choose Format Cells.
2. In the Format Cells dialog box, go to Number tab, and select General in the Category list.
3. Click OK. The date in the selected cell converts to a 5-digit number, which is Excel's internal way of storing dates. You can use this for calculations such as subtracting dates to find day differences or for system integrations that rely on date serials.
Note: If you need to revert the number back to a standard date format, simply follow the same steps and select a Date category instead.
2.2 Convert date to number in mmddyyyy or ddmmyyyy format
When you wish to transform a date into a number string formatted as mmddyyyy or ddmmyyyy (which is technically still a text string in Excel, but with digits only), use a custom number format. This is useful for generating uniform numerical date codes for sorting, import/export, or standardized document IDs.
1. Right-click the cell containing the date you want to convert, and choose Format Cells from the context menu.
2. In the Format Cells dialog, under the Number tab, choose Custom in the Category list. Then type your desired format code, such as mmddyyyy, into the Type field.
3. Click OK. The date now displays as a number string in your selected format.
Here are some other commonly used custom codes and the resulting display:
| Date | 2/23/2019 |
| Format Cell as custom | ddmmyyyy | ddmmyy | mmyyyy | yyyymmdd |
| Display | 23022019 | 230219 | 022019 | 20190223 |
Be careful: Although the number is visually shown, Excel still treats it as a date in the cell unless you use the TEXT formula for conversion. If you export or use the data in another system, verify whether the underlying cell value or the displayed text is being read.
If you want to convert date values quickly to month, day, year, or any other custom date format—even for batch operations—the Apply Date Formatting tool in Kutools for Excel can be very convenient. This is helpful for users who prefer to avoid complicated formulas or repetitive right-click actions, and especially for formatting a large range of dates in different styles.
After free installing Kutools for Excel, please follow these steps:
1. Select the cells containing the dates needing conversion.
2. Click Kutools > Format > Apply Date Formatting.
3. In the Apply Date Formatting dialog, select your desired output from the Date formatting pane. You can preview the result immediately in the right Preview pane.
4. Click OK to apply changes, and all the selected dates are instantly reformatted.
Tips: This method is especially helpful for batch processing and provides a visual preview, eliminating trial-and-error. If you encounter unexpected results, check the original data for invalid date entries or text-formatted dates, as these may not be properly recognized by the tool.
Excel's built-in Text to Columns feature can also be utilized to batch convert date values into text or different date/number representations, such as when cleaning imported data with inconsistent formats or preparing for data migration to other systems.
The advantage of this method is that it does not require formulas or VBA and can handle multiple cells or columns simultaneously. However, it may overwrite contents in columns to the right, so always ensure no valuable data exists in those columns or perform the conversion in a new area.
Steps:
- Select the range containing dates you wish to convert.
- Go to the Data tab on the ribbon and click Text to Columns.
- In the wizard, choose Delimited and click Next (Do not select any delimiter in the Step 2 of the wizard).
- Click Next again, then under Column data format, choose Date, and specify the correct incoming date format (such as MDY, DMY, YMD), or select Text to force conversion to text format.
- Choose the destination cell for output if you do not want to overwrite the original column.
- Click Finish.
After completion, the selected date values will be converted to the chosen format (text). This method is particularly effective for quickly enforcing text formatting on date strings or resolving issues with imported CSV/TXT files, where Excel may incorrectly auto-convert text dates.
Precautions: If output columns contain formulas or important data, move or back up data beforehand. For non-English dates or custom date patterns, double-check the "Date" dropdown to match the original data's arrangement. If unexpected results occur—for example, if dates display as numbers or fail to convert—double-check that your system regional date format matches your selection in the wizard.
In summary, Excel offers a variety of flexible solutions for converting dates to text or number formats, including formulas (TEXT), formatting options, powerful add-ins like Kutools, and built-in features such as Text to Columns. Each method is suited to a particular scenario: use formulas for dynamic results, formatting for simple appearance changes, Kutools for user-friendly bulk processing, VBA for large repetitive tasks, and Text to Columns when importing or cleaning textual data. Always choose the method that best matches your specific conversion need, data size, and workflow preference. Be cautious about formulas versus true cell values, and always keep backups of your data before performing large-scale conversions.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in