Excel from SAP cannot convert text to date

[jmustany]

Hi All,

I am trying all the formula to convert text to date but some rows doesn't work. This is a file from SAP. Anyone can give me a solution? Thanks

[#2]

@jmustany 

Option 1:

Select the Transaction column.

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click Next > twice.

In Step 3 of the Text to columns Wizard, select Date, then select DMY from the drop-down next to it.

Click Finish.

Option 2:

Use PowerQuery (Data > Get & Transform Data > From Table/Range)

Option 3:

Let's say the Transaction data are in D2 and down.

Enter the following formula in E2:

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

Format E2 as a date, then fill down.

[#3]

Option 1, actually works!
I spot my error when do delimiter. I shouldn't thick any of delimiter even though "." is the delimiter here. Thanks.