How to Auto Correct Syntax Error in Microsoft Excel like Date is entered in a column as 12.08.24, 12-08-2024, 12082024, 12.08.2024 and we want all the entries in a common format as 12.08.2024.

Question

How to Auto Correct Syntax Error in Microsoft Excel like Date is entered in a column as 12.08.24, 12-08-2024, 12082024, 12.08.2024 and we want all the entries in a common format as 12.08.2024.

Answer 1

I am assuming your dates are in Column A starting from A2 :

=TEXT(

   IF(LEN(A2)=8, 

      DATE(MID(A2,5,4), MID(A2,3,2), LEFT(A2,2)), 

      IF(LEN(A2)=10, 

         DATEVALUE(SUBSTITUTE(A2,".","/")), 

         IF(LEN(A2)=9, 

            DATEVALUE(SUBSTITUTE(A2,"-","/")), 

            IF(LEN(A2)=6, 

               DATE(2000+RIGHT(A2,2),MID(A2,4,2),LEFT(A2,2)), 

               DATEVALUE(A2)

            )

         )

      )

   ), 

   "dd.mm.yyyy"

)

If you want the solution in ADF please tell me :)

Answer 2

Hi,

Just checking in to see if the information of Amira Bedhiafi was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.