How to convert yyyymmddhhmmss date format to normal datetime in Excel?
In practice, it is common to encounter data exported from other systems or log files that present date and time as a single continuous string in yyyymmddhhmmss format. For example, “20240630174521” represents the date and time as 30/06/202417:45:21. However, Excel does not automatically recognize this string as a standard date-time value, which can make it challenging to analyze, sort, or perform calculations with these dates. Converting such strings to Excel’s recognizable datetime format (e.g., dd/mm/yyyy hh:mm:ss) is essential for further data analysis, filtering, or reporting.
This article provides practical step-by-step instructions to transform yyyymmddhhmmss strings into the standard Excel datetime format. We’ll cover multiple approaches, from using formulas for quick in-cell conversion, to automated VBA code solutions, as well as leveraging Excel’s Power Query for a more flexible, visual method. Each approach is suitable for different scenarios depending on your needs, data scale, and familiarity with Excel features.
Convert yyyymmddhhmmss to normal date time format with formulas
VBA Code - Convert yyyymmddhhmmss to normal date time automatically
Convert yyyymmddhhmmss to normal date time format with formulas
Excel formulas provide a quick, reliable way to split and reformat the yyyymmddhhmmss string into an Excel-recognized datetime value directly in your worksheet, without needing external add-ins or advanced tools. This approach is best suited for relatively small datasets or when you want a manual, transparent, and easily auditable transformation.
To convert the string, enter the following formula in a blank cell (for instance, in cell B2 if your original yyyymmddhhmmss value is in cell A2):
=TEXT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2)),"dd/mm/yyyy hh:mm:ss")Instructions: After entering the formula, press Enter to confirm. If you have a list of yyyymmddhhmmss values in column A, you can drag the fill handle from cell B2 down to quickly apply the formula to other rows. Each formula result gives you the formatted datetime as text.
This method is highly practical for ad-hoc data clean-up, small lists, or situations where you want to keep the original string intact in one column and the formatted value in another. Potential disadvantages: the formula result is still text; if you need Excel’s true datetime value for calculation (such as time differences), you might need to convert the text to a number format.
Parameter explanations and tips:
- LEFT(A2,4): extracts the 4-digit year
- MID(A2,5,2): extracts the 2-digit month
- MID(A2,7,2): extracts the 2-digit day
- MID(A2,9,2): extracts the 2-digit hour
- MID(A2,11,2): extracts the 2-digit minute
- MID(A2,13,2): extracts the 2-digit second
- The TEXT() function formats the result as a user-friendly date-time string.
Possible issues: If any of the original values are not exactly 14 digits (for example, missing leading zeros or accidentally entered as numbers in scientific notation), formulas may return incorrect results or errors. Double-check input consistency before using formula-based transformation.
Alternatively, if your data are always exactly 14 digits, this shorter formula converts the string and returns an Excel serial datetime value (not text):
=--TEXT(A2,"0000\-00\-00\ 00\:00\:00")How to use: After entering this formula in a cell (such as B2), press Enter. Then, you may need to format the resulting cell(s) as DD/MM/YYYY HH:MM:SS using the Format Cells dialog box to display the value in human-readable date-time format. To do so, select the result cells, right-click, choose Format Cells > Custom, and enter dd/mm/yyyy hh:mm:ss as the format code.
This second method is especially efficient if you want a real date value instead of a text string.
Tip: If you intend to use the converted datetime results in further date math calculations (e.g., subtracting dates, finding durations), always use the formula that returns a serial number (the second formula), and then format it as needed.
Troubleshooting: If you get errors, check if your source data are true text and contain no non-numeric characters. Leading or trailing spaces may also affect results; consider cleaning data by using TRIM(A2) within the formula if you suspect this problem.
VBA Code - Convert yyyymmddhhmmss to normal date time automatically
For users who need to handle large datasets or wish to automate the conversion process, VBA (Visual Basic for Applications) provides a flexible solution. By looping through selected cells, you can convert each yyyymmddhhmmss value into a standard Excel datetime value in-place or in a target column. This approach is very useful when you want to avoid writing formulas across multiple cells or when your data updates frequently.
This method is suitable for larger datasets or recurring needs. However, using VBA requires enabling macros, which may be restricted by your organizational policies or Excel settings. Before running any VBA code, remember to save your file, as changes are not always easily reversed.
Operation steps:
- Select the range of yyyymmddhhmmss values in your worksheet that you need to convert (e.g., A2:A100).
- Click Developer tab > Visual Basic to open the VBA editor. In the left-hand pane, find your workbook, right-click, select Insert > Module. In the code window, paste the following code:
Sub Convert_yyyymmddhhmmss_To_DateTime()
Dim rng As Range
Dim cell As Range
Dim strDate As String
Dim y As Integer
Dim m As Integer
Dim d As Integer
Dim hh As Integer
Dim mm As Integer
Dim ss As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range of yyyymmddhhmmss values to convert:", xTitleId, Selection.Address, Type:=8)
For Each cell In rng
strDate = Trim(cell.Value)
If Len(strDate) = 14 And IsNumeric(strDate) Then
y = CInt(Left(strDate, 4))
m = CInt(Mid(strDate, 5, 2))
d = CInt(Mid(strDate, 7, 2))
hh = CInt(Mid(strDate, 9, 2))
mm = CInt(Mid(strDate, 11, 2))
ss = CInt(Mid(strDate, 13, 2))
cell.Value = DateSerial(y, m, d) + TimeSerial(hh, mm, ss)
cell.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End If
Next cell
End SubAfter pasting the code, close the VBA editor. Return to Excel, and run the macro by pressing Alt + F8, selecting "Convert_yyyymmddhhmmss_To_DateTime", and clicking Run. This macro will prompt you to select the range and then transform each 14-digit numeric string into an easily readable date-time value in-place.
Tips and troubleshooting:
- If some values do not convert, check for length (should be exactly 14 digits), ensure no extra spaces, and confirm all values are numbers.
- You can copy the converted date-time values to another column before running the macro if you wish to keep the originals intact.
- Always save your workbook before running macros, and make sure macros are enabled in Excel.
Advantages: Quickly processes large ranges, easy to automate for repetitive tasks.
Disadvantages: Requires enabling macros, not always suitable in environments with macro security restrictions.
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.
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