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.

Try this in B1 and format -> yyyy/mm/dd hh:mm:ss

=--TEXT(A1,"0000\/00\/00 00\:00\:00")

Put this formula in B1:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

then copy down as required.

Hope this helps.

Pete

Thanks for the rep, glomb.

Pete