Monday, September 26, 2011

yogi_Compute Hours Worked By Date From Form Submittals And Display Those That Are Greater Than A Specified Number

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
question:
QUERY( QUERY( A:C ; "Select toDate(A), B, sum(C) Where B<>'' Group by B, toDate( A) ") ; "Select Col1, Col2, Col3 Where Col3>8 "; 1 )
I just found a problem with this formula,
Column A is timestamp date, Column B is a work code (Which doesnt matter), Column C is the hours worked.
I wanted a formula that showed all dates that had over 8 hours in a day, because sometimes there are multiple submissions in a day. This formula works, but I dont want it to factor in column B.
-----------------------------------------------------------------------
In my proposed solution in Sheet1 I have assumed that the work codes are TEXT strings