Google sheets query selecting year, month and day from a group of rows containing date and time as string

0
votes
=QUERY(Activity_2019, "SELECT A,F, YEAR(H), MONTH(H), DAY(H), sum(O) where A='Trades' group by G,A,F, YEAR(H), MONTH(H), DAY(H)",1)

I'm trying to sum rows which are grouped by identical cell values. This is working fine until I add also try to print the Day, Month and Year which I'm trying to grab from a string located in column H.

Here are 3 sample rows that produce a correct sum(O) result. Unfortunately, Column H contains a string which holds both date and time. The grouped rows have identical year, month and day and not time as you can see below. Can anyone help me grab the year, month and day only for the summed group? Is this possible with Google Sheets query?

Column H

2019-01-03, 09:30:32
2019-01-03, 09:31:17
2019-01-03, 09:35:01

Here is a sample google sheet to see what I mean. https://docs.google.com/spreadsheets/d/1-C9bMX687ViakkTf5hXBldeFeu7xRtShTx6RfiYGiS4/edit#gid=0

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
share a copy of your sheet with example of desired output – player0
Thanks. Added a link to a sheet in the description. – domid

1 Answers

0
votes

delete all in range D:F and paste this in D1 cell:

=ARRAYFORMULA({"Year", "Month", "Day"; 
 IFERROR(SPLIT(VLOOKUP(A2:A&B2:B, {Data!A:A&Data!G:G, 
 TEXT(INDEX(SPLIT(Data!H:H, ","),,1), "yyyy♦mm♦dd")}, 2, 0), "♦"))})

0