CODEWITHSUNDEEP
google-sheets-formula
PriyankaJ
PriyankaJ

Reputation: 390

Google sheets Query function convert string to date

Here is the link to the sample data and query function. Currently it returns the date as string as the raw data has string. How can I convert it to date?

My current query looks like this : query(A2:C, "select A, sum(B) , sum(C) where A is NOT NULL group by A label sum(B) '' , sum(C) ''",0)

https://docs.google.com/spreadsheets/d/13o8ZaOJmrpgNmbTCU98yzhFpv_ALfil4RGwg9v5QlIw/edit?usp=sharing

Upvotes: 0

Views: 993

Answers (1)

Aresvik
Aresvik

Reputation: 4630

Locale for the Sheet is 'United States' but the entered date-type values are typically European (dd/mm/yyyy), therefore try:

=arrayformula(query({datevalue(regexreplace(to_text(A2:A),"(.|..)[\/\-\.](.|..)[\/\-\.](.*)","$2\/$1\/$3")),B2:C}, "select Col1, sum(Col2) , sum(Col3) where Col1 is NOT NULL group by Col1 label sum(Col2) '' , sum(Col3) ''",0))

enter image description here

Upvotes: 1

Related Questions