How to quickly convert mm/dd/yyyy date format to yyyymmdd in Excel?
Working with dates in Excel often requires converting them between different formats for data processing, reporting, or importing into other systems. While it's common to change a yyyymmdd string back into a standard Excel date (mm/dd/yyyy), you may also need to perform the reverse: convert an mm/dd/yyyy date into a continuous yyyymmdd text string, as illustrated in the screenshot below. This kind of conversion is especially useful when exporting data for systems that require the compact yyyymmdd format, such as databases, data uploads, or legacy software. This article provides several practical methods to perform this conversion, helping you gain flexibility in preparing and managing your date data in Excel. 
Convert mm/dd/yyyy to yyyymmdd with Format Cells
Convert mm/dd/yyyy to yyyymmdd with formula
Convert mm/dd/yyyy to yyyymmdd with VBA Code
Convert mm/dd/yyyy to yyyymmdd with Format Cells
If you want to display your existing Excel dates (mm/dd/yyyy) in the yyyymmdd format, you can use the Format Cells feature. This solution is straightforward and doesn't modify the underlying date value — only how it's shown.
1. Select all the date cells you want to convert to the yyyymmdd appearance. Then, right-click inside the selection to open the context menu, and choose the Format Cells option. See screenshot: 
2. In the Format Cells dialog box, go to the Number tab. Select Custom from the Category list on the left. In the Type field to the right, enter yyyymmdd. See screenshot: 
3. Click OK to apply your changes. Now the selected dates will appear as a continuous yyyymmdd string in each cell, while the cell values remain as date serial numbers in the background, which means they're still fully usable for date calculations and sorting. 
Note: If you copy and paste these formatted cells to other locations as values (using Paste Special > Values), Excel will convert the date to a five-digit number representing the underlying date serial. This might not be what you want. To retain the yyyymmdd text, use a formula-based or VBA approach (see below), or after copying, use Paste Special > Values > Text and check your results. 
- Applicable Scenarios: Best for quick formatting where you only need dates visually displayed as yyyymmdd without actually converting the value type; ideal for reports, dashboard views, or printing purposes.
- Limitations: May cause issues if you need to export the yyyymmdd dates as actual text strings, especially when pasting into other applications or files.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Convert mm/dd/yyyy to yyyymmdd with formula
If you require the yyyymmdd format as actual text values for further processing, exporting, or database use, a simple Excel formula can efficiently convert standard Excel dates (mm/dd/yyyy) to continuous digit strings. This method is especially suitable when you need to keep the new date representation as true text, for example, for system imports or concatenating dates with other data.
Select a blank cell where you want the converted yyyymmdd text to appear (for instance, B1), and enter the following formula:
=TEXT(A1,"yyyymmdd")
After entering the formula, press Enter. To apply this conversion to multiple rows, drag the fill handle down the column to copy the formula to other cells. Each result is a text string in the yyyymmdd format. To convert all results to static text, you can copy the formula cells and use Paste Special > Values where needed. 
- Applicable Scenarios: Ideal for batch converting dates to text strings, preparing data for export, or concatenating dates with other fields (like creating IDs or keys).
- Limitations: The output cell contains text, so it cannot be used in date calculations unless converted back to a date format.
Convert mm/dd/yyyy to yyyymmdd with VBA Code
When you are working with a large data set or need to automate repetitive conversions from standard mm/dd/yyyy Excel dates to yyyymmdd text strings, a VBA macro provides an efficient solution. This method is highly adaptable, allowing you to process entire columns or workbooks at once, and is suitable for users looking to streamline data preparation without manual formula entry.
1. Open the VBA editor by clicking Developer tab > Visual Basic. In the opened Microsoft Visual Basic for Applications window, select Insert > Module, and paste the following code into the module:
Sub ConvertToYYYYMMDD()
'Updated by Extendoffice 20250723
Dim WorkRng As Range
Dim rng As Range
Dim CellVal As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to convert to yyyymmdd:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
For Each rng In WorkRng
If IsDate(rng.Value) Then
rng.NumberFormat = "@"
rng.Value = Format(rng.Value, "yyyymmdd")
End If
Next
WorkRng.Columns.AutoFit
End Sub2. Press 
(Run) or press F5 to execute the macro. A dialog box will appear to prompt you to select the range containing your dates. After confirming, all valid dates in your selected range will be instantly converted to the yyyymmdd text string format in place.
- Applicable Scenarios: Highly effective for automating batch conversions across large worksheets or when you need to process similar tasks repeatedly. Especially useful for advanced users managing dynamic data imports, reporting, or when integrating with external systems.
- Precautions & Tips: This macro will overwrite your original data in the selected range. It's recommended to backup your worksheet or test the macro on a sample set before applying it widely. The macro only processes cells that already contain date values; cells with non-date entries will remain unaffected.
- Troubleshooting: If you do not see the Developer tab, enable it via File > Options > Customize Ribbon. If the input range includes text or empty cells, these will not be changed by the macro.
Add Days, Years, Months, Hours, Minutes, and Seconds to Dates in Excel with Ease |
If you have a date in a cell and need to add days, years, months, hours, minutes, or seconds, using formulas can be complicated and hard to remember. With Kutools for Excel’s Date & Time Helper tool, you can effortlessly add time units to a date, calculate date differences, or even determine someone's age based on their birthdate - all without needing to memorize complex formulas. |
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now |
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