How to convert yyyymmdd to normal date format in Excel?
Maybe, there are multiple dates in yyyymmdd format, and now you want to convert them to the normal date format, such as mm/dd/yyyy, as shown in the screenshot. If you simply apply the Format Cells feature in Excel, you may get a result like ###########. So, how can you correctly convert yyyymmdd values to normal dates in Excel?
Convert yyyymmdd to normal date format with formula
Convert yyyymmdd to normal date format with Text To Columns feature
Convert yyyymmdd to normal date format with VBA code
Convert yyyymmdd to normal date format with Kutools for Excel
| Feature | Formula | Text to Columns | VBA Code | Kutools for Excel |
|---|---|---|---|---|
| Best for | Converting consistently structured YYYYMMDD values while keeping the source data | Quick one-time conversion with Excel’s built-in tools | Automating repeated YYYYMMDD conversions | Quickly converting large ranges or various nonstandard date formats |
| Suitable for YYYYMMDD | Yes | Yes | Yes | Yes |
| Handles other nonstandard date formats | Limited; formula normally matches a fixed structure | Limited; best for consistent YMD data | Limited unless the code is modified | Yes; designed to handle various nonstandard date formats |
| Recovery / Undo support | Source data remains if using a helper column | Standard Excel undo may be available | No built-in recovery in the macro | Yes; the article provides a Recover option |
| Main advantage | Flexible and built into Excel | No formulas or code required | Reusable and suitable for automation | Fast batch conversion with fewer manual steps |
| Main limitation | Requires a formula and helper cells | Requires several wizard steps | Requires VBA knowledge and macro execution | Requires Kutools for Excel |
| Recommended use | Best for smaller datasets or formula-based control | Best for quick, one-time conversion of consistent data | Best for repeated automated conversions | Best for large datasets or mixed nonstandard date formats |
Convert yyyymmdd to normal date format with formula
The following simple formula may help you to convert the yyyymmdd to normal date format, please do as follows:
1. Please enter this formula: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) into a blank cell besides your data, see screenshot:
2. Then drag the fill handle down to the cells that you want to contain this formula, and the data in column A has been converted to the normal date as follows:
Convert various non-standard date format to real date format with only one click
If you have multiple non-standard date format, such as yyyymmdd format, the normal Format Cells feature in Excel will not work well for you. With Kutools for Excel’s Convert to Date utility, you can quickly convert various non-standard dates to your locale date formats. Click to download Kutools for Excel!
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!
Convert yyyymmdd to normal date format with Text To Column functiona
The Text To Column function in Excel also can convert this date format to the normal date, follow the instructions step by step:
1. Select the data range that you want to convert.
2. Click Data > Text To Column, see screenshot:
3. In step 1 of Convert Text to Column Wizard, select Delimited option, see screenshot:
4. Then click Next > Next go to the Step 3 of 3 wizard, in this step, select Date under the Column data format, then choose YMD from the drop down list, see screenshot:
5. And then click Finish button, the yyyymmdd format has been converted to the mm/dd/yyyy date format. See screenshots:
 |  |  |
Convert yyyymmdd to normal date format with VBA code
If you are interested in VBA code, here, I will provide a code for you to solve this task.
1. Please hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module window.
VBA code: convert yyyymmdd to normal date format
Sub ConvertYYYYMMDDToDate()
'Updateby Extendoffice
Dim x As Range
Dim Workx As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set Workx = Application.Selection
Set Workx = Application.InputBox("Range", xTitleId, Workx.Address, Type:=8)
For Each x In Workx
x.Value = DateSerial(Left(x.Value, 4), Mid(x.Value, 5, 2), Right(x.Value, 2))
x.NumberFormat = "mm/dd/yyyy"
Next
End Sub
3. After pasting the code, then press F5 key to run this code, and select the data range that you want to use from the popped out dialog, see screenshot:
4. And then click OK, the data in the range has been converted to date format.
Convert yyyymmdd to normal date format with Kutools for Excel
Except the above yyyymmdd format, sometimes, you may have other types of non-date format data need to be converted to the normal date format, in this case, I can introduce a useful tool--Kutools for Excel, with its Convert to Date feature, you can quickly convert various non-date format data to real date format.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to convert.
2. Click Kutools > Content > Convert to Date, see screenshot:
3. And you will get all the selected data has been converted to the standard date format, see screenshot:
Note: You can highlight all the records in the Convert to Date dialog box, and click Recover to cancel this operation and return to your original data as following screenshot shown:
Download and free trial Kutools for Excel Now !
Conclusion
Converting YYYYMMDD values into real Excel dates makes the data easier to sort, filter, calculate, and display in different date formats.
Use a formula when you want to keep the original values and create converted dates in a helper column. Text to Columns is a convenient built-in option for quick, one-time conversions, while VBA is better suited to repeated or automated tasks. For larger ranges or mixed nonstandard date formats, Kutools for Excel provides a faster batch-conversion approach with fewer manual steps.
Tip: Before converting a large range, check that the source values follow the expected date structure. For example, YYYYMMDD data should contain the year, month, and day in a consistent order.
Choose the method based on your data size, whether you need to preserve the original values, and whether the task is a one-time conversion or something you need to repeat regularly.
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