Excel convert date format from YYYYMMDDHH24MISS to DD MM YYYY HH24 MI SS

Convert yyyymmddhh24miss to dd/mm/yyyy hh24:mi:ss

Recently came across an excel issue where pivot table was not recognizing the date imported in format YYYYMMDDHH24MISS.

This caused grouping of date column to fail.

I used the following formula to convert the date format - 

=TEXT(DATE(LEFT(G6,4),MID(G6,5,2),MID(G6,7,2))+TIME(MID(G6,9,2),MID(G6,11,2),MID(G6,13,2)),"dd/mm/yyyy hh:mm:ss") 

After format conversion, the date column format appeared as Custom, but still the column was not grouping in pivot. Neither i was able to group the date column on enabling filter.

To overcome this gaffe, i added another column with free text AM / PM based on time of day.

Concatenated the Date column with free text in another column [= Concat("DATE", "AM_PM")] - named it Column_WIPE

Copied the contents of Column_WIPE in another column as Paste_Special - Value Only, named it Column_SHOT.

In the Column_SHOT using Find and Replace (CTRL + F), searched for both AM and PM, replaced then with null - 

This converted the Column_SHOT contents to Long date and the column was groupable.

Not sure how it worked, 

                       I tried  multiple workarounds earlier, but noting worked except this custom workaround.

Hope someone find this helpful.