How to convert yyyymmddhhmmss date format to normal datetime in Excel?
Maybe you have a worksheet which contains some date time format as this: yyyymmddhhmmss, but now, you want to convert it to the normal date time format dd/mm/yyyy hh:mm:ss as following screenshot shown. How could you deal with this task in Excel?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following formulas may help you to convert the yyyymmddhhmmss date time format to the normal date time format as you need. Please do as this:
Enter this formula:
=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") into a blank cell where you want to locate the result, and then drag the fill handle down to the cells that you want to contain this formula, and all the date time has been converted to the normal date time as usual, see screenshot:
1. In the above formula, A2 is the cell that you want to convert.
2. Except the above formula, here is another simple formula also can help you, please enter this formula: =--TEXT(A2,"0000\-00\-00\ 00\:00\:00") into a blank cell to get the following results:
Then format the values as DD/MM/YYYY HH:MM:SS date time format as you need in to the Format Cells dialog box to get the normal date time format.