Mar 03 2023 04:55 AM
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
Mar 03 2023 05:01 AM
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.
Mar 03 2023 07:14 AM