Date / Time formatting

Hello,

I'm exporting data from SAP which annoyingly has the date format as yyyymmddhhmmss and displayed as a number.

I've tried all different formats listed in Excel and it always returns ##############. I edited a single cell and manually inputted the relevant slashes and colons so that it was displayed yyyy/mm/dd hh:mm:ss which worked but then when I tried dragging it down to format the other cells, they change to ############.

All I really need is the date, I'm not fussed about the time. Any help will be greatly appreciated. I've attached an example spreadsheet.