Google-sheets – Cannot get Query to display time

google sheetsgoogle-sheets-query

I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I got it to show the coins from May 25, but cannot get the Duration, Days, MTD Coins and MTD Hours for May 25 to show.

https://docs.google.com/spreadsheets/d/1XJyPEqNMxi2EhzSiUYpYOBMv5m59HD2dLx_Y7rFtGMs/edit?resourcekey#gid=958394600

Here's what should happen. Selecting the date in C22 should display "Laugene's" coins, Duration, Days, MTD Coins and MTD Hours.

Issues encountered (Row 24):

  1. Duration, Days, MTD Coins and MTD Hours are not showing.
  2. Total Duration and Days are not summing up on Col BN and BM

Any thoughts or suggestions?

Best Answer

The query() function has a limitation that prevents the summing of date, time and duration values. The workaround is to use value() to convert such datetime values to pure numbers before aggregation. The result will be a dateserial number. You can format the result cell as a datetime to show the values in a readable format.

To get the names, coins and durations for the date selected in B22:D22, clear the range A23:C25 and put this formula in cell A23:

=arrayformula( 
  query( 
    { 
      'Form Responses 1'!A1:E, 
      value('Form Responses 1'!F1:F), 
      'Form Responses 1'!G1:G, 
      text('Form Responses 1'!E1:E, "mmmmddyyyy") 
    }, 
    "select Col2, sum(Col4), sum(Col6) 
     where Col2 is not null 
     and Col8 = '" & B22 & C22 & D22 & "' 
     group by Col2 
     label sum(Col4) 'Coins', sum(Col6) 'Duration' ", 
    1 
  ) 
)

To get days, MTD coins and MTD hours, put these formulas in D24:F24 and fill them down:

=countunique( 
  iferror( 
    filter( 
      'Form Responses 1'!E$2:E, 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)

=sum( 
  iferror( 
    filter( 
      'Form Responses 1'!D$2:D, 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)

=sum( 
  iferror( 
    filter( 
      value('Form Responses 1'!F$2:F), 
      text('Form Responses 1'!E$2:E, "mmmmyyyy") = B$22 & D$22, 
      'Form Responses 1'!B$2:B = A24 
    ) 
  ) 
)

See query() and filter().

See this answer for an explanation of how date and time values work in spreadsheets.

Related Topic