Data couldn't change to date format-CodePudding

I am a beginner making formula. I have pull some data from several google sheet using QUERY IMPORTRANGE which few column contain date. I can easily change the date to date using formatting feature. But only one column I couldn't change format to date. I've tried using

=DATEVALUE , =TO_DATE, =DATE(mid, right, left) but doesn't work

Examples of my data is as below (dd/mm/yy):

11.01.23
28.03.23
18.12.22

Please help me. tqvm

I've tried using =DATEVALUE , =TO_DATE, =DATE(mid, right, left) but doesn't work

CodePudding user response：

This should work-

=INDEX(DATE("20"&RIGHT(A1:A3,2),MID(A1:A3,4,2),LEFT(A1:A3,2)))

Basic formula for single cell =DATE(2000 RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

CodePudding user response：

try:

=INDEX(SUBSTITUTE(A1:A3; "."; "/")*1)