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
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
Wow... Amazing. You have skills. Are you able to give me a hand with a formula that I have. Similar idea but different? Mrjkirby@gmail.com
ReplyDelete=if(value(mid(text(log!$A:$A; "yyw");3;5))=(value(mid(text(AY$2; "yyw");3;5))), (filter(log!$E:$E, search($B$3,log!$B:$B)),(0)))