Incorrect date formate downloaded from SAP | Excel forum

Hi Leila

Thanks for the data. For this I would use a combination of the DATE, LEFT, RIGHT and MID functions.

Assuming your data starts in A1, I would put another column adjacent to it and enter the following formula:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

The DATE function creates a date out of its constituent parts (the day, the month, the year). We extract these parts from the source data using the LEFT, RIGHT and MID functions. In turn each of these looks at the cell and pulls out a certain number of characters.

RIGHT(A1,4) extracts 4 characters from the right of A1.

LEFT(A1,2) extracts the first 2 characters from cell A1.

MID(A1,4,2) counts 4 characters in from the left, then extracts 2 characters.

After you've assembled the date, you can then format the cell using your preferred date format.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector