I am getting the dates in the following format:
5.9.2024
. so d.m.yyyy
.
and I need to use that field in the query formula in order to check if that date falls between two dates (e.g. >=5.9.2024
. and <=12.9.2025
. are the values of the cells for the date range).
I am struggling to convert the text cell to date so that I can do this comparison.
I tried all different formulas for text to date conversion, but nothing works.
=query(A4:B, "select Col1 where Col2>='"&$A2&"'AND Col2<='"&$B2&"'")
You may try the following formula-
=--TEXTJOIN("/",1,CHOOSECOLS(SPLIT(A1,"."),2,1,3))
Then apply your desired date format to cells.
To convert entire column, use-
=MAP(A1:A,LAMBDA(x,IF(x="",,--TEXTJOIN("/",1,CHOOSECOLS(SPLIT(x,"."),2,1,3)))))